Help with Security Model

Hi all,

I need some sugestions from all of you about setting up security model in
our SQL2000 box.

The server was setup using Mixed mode. However, all the applications
(web and MS access) access the server using "sa" userid.

There are several databases in our server. Ex: (DB1,DB2,DB3,DB4 and DB5)

Application 1: need read/write access to DB1,DB2 and DB3
Application 2: need read/write access to DB5
Application 3: need read/write access to DB4 and DB3

Should I set up three userids and give them the dbo access to those
database that they need to use?

Does that make any sense to you?

Thank you for all your suggestionThe 'sa' account should NEVER be used by ANY application or user (other than the DBA, and then carefully.)

Use three different UID/Pwd for the applications and GRANT permissions that way.|||In addition, unless they really NEED dbo, just GRANT the minimum they need.|||I'm a firm believer in the "principle of least privlege", meaning you create as many accounts as you need and only give each one the privleges that it needs to do its job. Check out the predefined database roles (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_help_5omd.asp) to see if your accounts even need dbo, or if a combination of db_datareader and db_datawriter would do.

-PatP|||I take it a bit further than data_reader/writer and grant Select/Insert/Update/Delete permissions as required. For instance, a financial application allows Inserts but NO updates. Once a record has been inserted, that's the way it stays. Corrections are made by inserting another record to adjust the transaction (along with an explanation.) Auditors seem to prefer this for following the money trail. Good thing I'm sa... :)|||Thank you for all your suggestions.

help with linked server on sql2000

hello,
I'm trying to create a linked server from an SQL2000 to a Unify ELS
(very old, odbc is version 1) database on SCO unix.
The odbc driver is old but it works fine when used by applications for
creating reports.
What I want to do is make a linked server from the SCO box to the win2k
box. So, I'm using enterprise manager to create the linked server.
The DSN of the unify database is CORE_OFFICE1

I'm getting 'Error 7399: OLE DB provider 'MSDASQL' reported an error.
Datasource name not found and no default driver specified.

When I create the linked server I chose the OLE DB for ODBC drivers.
I put 'CORE_OFFICE1' for the Data Source name. I also setup the
security with the name of a unix account for login/password.
What else should I have?

I'm new at this linked server stuff!

Product Name:
Data Source:
Provider String:
Location:
Catalog:

Thanks,

Oskarpheonix1t <pheonix1tAThoustonDOTrrDOTcom@.com.com> wrote in message news:<vvVTc.3077$np.309@.newssvr22.news.prodigy.com>...
> hello,
> I'm trying to create a linked server from an SQL2000 to a Unify ELS
> (very old, odbc is version 1) database on SCO unix.
> The odbc driver is old but it works fine when used by applications for
> creating reports.
> What I want to do is make a linked server from the SCO box to the win2k
> box. So, I'm using enterprise manager to create the linked server.
> The DSN of the unify database is CORE_OFFICE1
> I'm getting 'Error 7399: OLE DB provider 'MSDASQL' reported an error.
> Datasource name not found and no default driver specified.
> When I create the linked server I chose the OLE DB for ODBC drivers.
> I put 'CORE_OFFICE1' for the Data Source name. I also setup the
> security with the name of a unix account for login/password.
> What else should I have?
> I'm new at this linked server stuff!
> Product Name:
> Data Source:
> Provider String:
> Location:
> Catalog:
> Thanks,
> Oskar

At the risk of asking a silly question, have you created and tested
the DSN successfully on the MSSQL server itself, and is the DSN a
system DSN? Assuming it works OK from Control Panel, then I would try
adding the linked server using Query Analyzer:

EXEC sp_addlinkedserver
@.server = 'Unify',
@.provider = 'MSDASQL',
@.datasrc = 'CORE_OFFICE1'

Simon|||Simon Hayes wrote:

> pheonix1t <pheonix1tAThoustonDOTrrDOTcom@.com.com> wrote in message news:<vvVTc.3077$np.309@.newssvr22.news.prodigy.com>...
>>hello,
>>I'm trying to create a linked server from an SQL2000 to a Unify ELS
>>(very old, odbc is version 1) database on SCO unix.
>>The odbc driver is old but it works fine when used by applications for
>>creating reports.
>>What I want to do is make a linked server from the SCO box to the win2k
>>box. So, I'm using enterprise manager to create the linked server.
>>The DSN of the unify database is CORE_OFFICE1
>>
>>I'm getting 'Error 7399: OLE DB provider 'MSDASQL' reported an error.
>>Datasource name not found and no default driver specified.
>>
>>When I create the linked server I chose the OLE DB for ODBC drivers.
>>I put 'CORE_OFFICE1' for the Data Source name. I also setup the
>>security with the name of a unix account for login/password.
>>What else should I have?
>>
>>I'm new at this linked server stuff!
>>
>>Product Name:
>>Data Source:
>>Provider String:
>>Location:
>>Catalog:
>>
>>Thanks,
>>
>>Oskar
>
> At the risk of asking a silly question, have you created and tested
> the DSN successfully on the MSSQL server itself, and is the DSN a
> system DSN? Assuming it works OK from Control Panel, then I would try
> adding the linked server using Query Analyzer:
> EXEC sp_addlinkedserver
> @.server = 'Unify',
> @.provider = 'MSDASQL',
> @.datasrc = 'CORE_OFFICE1'
> Simon

Yes, the odbc driver is installed on the sql server box. However, I had
it as a User DSN instead of a System DSN. I changed that and now I get
this error:
Error 7399, authentication failed. Data source rejected connection attempt.

I put the 'sa' user as the local account on the linked server security
tab and I put the unix user/password as the remote account.
I think this is getting closer, but it's still not there!

Thanks,

Oskar

ps. In the odbc driver configuration, I put the DSN name, the server IP
address, the remote user account/password. On the USER DSN tab, when I
open it again, I can see all the details I put - they stayed there.
But on the SYSTEM DSN, I do the same thing but when I open the driver
config. again after I've entered the details the only thing that is
visible is the DSN. Everything else is blank. Why is that?|||Simon Hayes wrote:

> pheonix1t <pheonix1tAThoustonDOTrrDOTcom@.com.com> wrote in message news:<vvVTc.3077$np.309@.newssvr22.news.prodigy.com>...
>>hello,
>>I'm trying to create a linked server from an SQL2000 to a Unify ELS
>>(very old, odbc is version 1) database on SCO unix.
>>The odbc driver is old but it works fine when used by applications for
>>creating reports.
>>What I want to do is make a linked server from the SCO box to the win2k
>>box. So, I'm using enterprise manager to create the linked server.
>>The DSN of the unify database is CORE_OFFICE1
>>
>>I'm getting 'Error 7399: OLE DB provider 'MSDASQL' reported an error.
>>Datasource name not found and no default driver specified.
>>
>>When I create the linked server I chose the OLE DB for ODBC drivers.
>>I put 'CORE_OFFICE1' for the Data Source name. I also setup the
>>security with the name of a unix account for login/password.
>>What else should I have?
>>
>>I'm new at this linked server stuff!
>>
>>Product Name:
>>Data Source:
>>Provider String:
>>Location:
>>Catalog:
>>
>>Thanks,
>>
>>Oskar
>
> At the risk of asking a silly question, have you created and tested
> the DSN successfully on the MSSQL server itself, and is the DSN a
> system DSN? Assuming it works OK from Control Panel, then I would try
> adding the linked server using Query Analyzer:
> EXEC sp_addlinkedserver
> @.server = 'Unify',
> @.provider = 'MSDASQL',
> @.datasrc = 'CORE_OFFICE1'
> Simon

Also, when I do the above, I get this:
Server: Message 15429, Level 16, State 1, Procedure sp_addlinkedserver,
line 72, '(null)' is an invalid product name|||pheonix1t (pheonix1tAThoustonDOTrrDOTcom@.com.com) writes:
> Simon Hayes wrote:
>> EXEC sp_addlinkedserver
>> @.server = 'Unify',
>> @.provider = 'MSDASQL',
>> @.datasrc = 'CORE_OFFICE1'
>>
>> Simon
> Also, when I do the above, I get this:
> Server: Message 15429, Level 16, State 1, Procedure sp_addlinkedserver,
> line 72, '(null)' is an invalid product name

It seems you need to specify the @.srvproduct parameter. However, I
believe that it is sufficient to pass an empty string.

As for the login problem, I decline. I'm not very good at setting
up linked-server logins myself.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp