Permissions to See Server Logins/Create Database Users

Our company has 2 Database Roles (DBE and DBA). The DBE creates

database schema, performs SQL Server Administration, and manages server

security. The DBA writes data access, ETL, and manages database

security. In 2005, we're struggling with how to allow the DBA to see

all of the logins on the server in order to add them as users of their

database. What permissions does the DBA need to select from any of the

logins on the server to add them to their database?

Michelle

Note that to add a user to a database, the dba does not need to be able to see the login's metadata - he only needs to know the login's name.

To see the information about a login, you need VIEW DEFINITION permission on that login.

To see information about all logins, you would need VIEW ANY DEFINITION permission, but this permission allows you to see more than just login information, so I don't recommend granting this permission. Instead, you can look at creating a procedure to return the necessary login information and sign the procedure with a certificate that has VIEW ANY DEFINITION permission.

Thanks
Laurentiu

sql

Permissions to See Server Logins/Create Database Users

Our company has 2 Database Roles (DBE and DBA). The DBE creates database
schema, performs SQL Server Administration, and manages server security. The
DBA writes data access, ETL, and manages database security. In 2005, we're
struggling with how to allow the DBA to see all of the logins on the server
in order to add them as users of their database. What permissions does the
DBA need to select from any of the logins on the server to add them to their
database?Michelle (Michelle@.discussions.microsoft.com) writes:
> Our company has 2 Database Roles (DBE and DBA). The DBE creates database
> schema, performs SQL Server Administration, and manages server security.
> The DBA writes data access, ETL, and manages database security. In 2005,
> we're struggling with how to allow the DBA to see all of the logins on
> the server in order to add them as users of their database. What
> permissions does the DBA need to select from any of the logins on the
> server to add them to their database?
VIEW ANY DEFINITION is the simplest - then the DBA will see all logins.
But he will also see other logins.
The other alternative is to grant VIEW DEFINITION on the logins he should
be permitted to play with.
Curiously there is no VIEW ANY LOGIN. There is ALTER ANY LOGIN, but that
would give the DBA permissions he should not have.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx