How to give DB's (SQL DB), separate passwords?

[mandianie]

In SQL Server 2000, can we give separate passwords to two separate databases created by two separate users in a single machine, irrespective of the password given wholly to SQL Server.

Eg. Suppose A & B are two databases. Can we give X1 password to A and X2 password to B, whereas XX will be the SQL Server password.

[si_the_geek]

Hi mandianie, welcome to VBForums!

The "SQL Server password" you mention is the System Administrator (SA) password. This user has full access to all databases on the server. This password should not be used for normal connections to the server as it gives too much power (you can destroy the database or lock people out of the databases if you want to), and should only be used for maintenance/support and database creation purposes.


You can create extra logins (each with their own passwords) on the server using the Security section of the Enterprise Manager application (as SA), and give permissions to those logins to access one or more databases, or parts of databases (eg: particular tables), and different permission levels for each item (eg: read table/insert into table/delete from table/etc).

You can also assign Windows logins to have permissions (rather than create new logins), which means that no password has to be entered by the user, as Windows has already verified it.

It is these logins which you should connect to the databases with.