sa Account Access Question

[Gabriel2010]

Hello!
Hope all is well mates!

Quick one:

Does the sa account in one SQL 2000 server have complete access to all databases by default?

When creating an SQL driver (ODBC>system dsn), does the account initially has to be the sa account to connect to SQL2k server?

Thanks in advance!



Glad to be here!

 

[nigelrivett]

>> Does the sa account in one SQL 2000 server have complete access to all databases by default?

yes

When creating an SQL driver (ODBC>system dsn), does the account initially has to be the sa account to connect to SQL2k server?

no

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Gabriel2010]

Ok.

It answers the questions.

WHat account can then be use to initially connect to SQL 2k in order to setup system dsn?

I needed to do this on a remote client to connect to an sql2k where a third application database is siitting.

Should the account used be a windows login account?

Or the third party application user account when creating the system dsn?

Thanks again!

Glad to be here!

 

[SQLBill]

Any LOGIN that has access to the data can be used to set up an ODBC connection.

I am the DBA and set up ODBC connections on other computers so my users can access the data via Crystal Reports.

First I create a LOGIN in SQL Server for the user (I use SQL Authentication due to the use of non-trusted domains). Then I give the LOGIN proper accesses to the database(s). Then I go to the computer that needs to make a connection and set up the ODBC using my information. However, sometimes this can't be done (for example, I have some remote users), so where I can't set it up, the user themselves set it up using their login information.

-SQLBill

 

[Gabriel2010]

"Any LOGIN that has access to the data can be used to set up an ODBC connection." Does this means if a third party application has an SQL databse on server, creating an account within the 3rd application allow the user to create system dsn & connect remotely to it?

"Then I go to the computer that needs to make a connection and set up the ODBC using my information." You mean "my information" meaning the Sa account or user account you created? The client system is remotely out in another continent and connects via a VPN.

I 'd like to understand if it was necessary to use the sa account for initial system connectivity to sql.

Thanks again mate!




Glad to be here!

 

[SQLBill]

1. Not really. To connect to any SQL Server database, a user must have a Security Login with SQL Server. The Login tells SQL Server how the user will authenticate. So to answer your question, there would also have to be a matching Login in SQL Server.

Go to Enterprise Manager, on the left side (tree view) expand until you see SECURITY. Expand that and you will see LOGINS. Click on that and you will see the logins that have access to SQL Server. Right click on a LOGIN, select Properties, go to Database Access tab and you will see which databases that LOGIN is a USER of. The General tab will also show you the authentication method.

2. You can initially make the connection with any valid LOGIN that is a USER of the database you are trying to connect to. Yes, you can use SA. No it's not necessary (unless no other LOGIN has user access to the database). I use my personal user login.

Hope that clarified it.