Allow db_owner to add/view existing logins to database

Question

On our development server (SQL 2008 R2) we want local dbo's to be able to add existing logins on the server to the database. The problem is they can't view them, they can only see themselves in the list. I know this was a security change in later versions of SQL Server to separate/restrict permissions more, but in our development environment it is just a pain.

I came across a post that suggested giving access to several server metadata views to the public role. As this would make things more like SQL 2000. (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104997 which internally references http://technet.microsoft.com/en-us/magazine/cc161026.aspx)

GRANT VIEW ANY DEFINITION TO public
GRANT VIEW SERVER STATE TO public

But this seems like giving too much permissions over to the public role. I just want to give the dbo's just enough rights to the system metadata views to allow them to browse for the existing logins on the server. That way they can select who they need to add without having to message the server admins each time.

Does anyone know the minimum set of views that should be granted permissions in order to allow this?

Thanks!

Answer 1

Isn't GRANT VIEW DEFINITION ON LOGIN::Whoever TO [domain\dbogroup] in master enough?
This is implied by MSDN (I think, can't test) in GRANT Server Principal Permissions

Failing that, one quirk that may be useful for SQL Server 2005+

This may give no rows

SELECT * FROM sys.server_principals WHERE name = 'domain\somegroup';

But this will give a number if it exists

SELECT SUSER_ID('domain\somegroup');

Note: SQL Server 2012 has contained databases where logins are at the DB level

Answer 2

Well first you shouldn't ever be granting rights to public.

You'll want to create a domain group and put all the developers in that group. Then grant them the ability to view. There's no easy way to allow a non-privileged login the right to see all the users at the instance level without allowing them to create new logins as there is no VIEW DEFINITION right which can be granted to all logins easily. To do this you would probably need to create a domain group, with the needed devs in it, then setup a job which nightly grants the group the VIEW DEFINITION right for each of the logins on the instance.

When SQL 2012 comes out this gets a bit easier with user defined server roles, but that doesn't really help you now.