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)


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?


2 Answers 2


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

  • I think the OP wants to do it with db principals. You're answer is the way he should be doing it though. +1 May 21, 2012 at 0:17
  • I just realized this is an old post. Sorry for coming into the game late. :) May 21, 2012 at 0:19

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.

  • I Agree not to look at the public role. In my own environment I did create a Windows group for developers, We grant them the ALTER ANY LOGIN and ALTER ANY CREDENTIAL Permission (we use a lot of SSIS), we avoid to grant CONTROL SERVER that allows to change sysadmin passwords although this would implie most of the permission they need. This way we allow them to only change logins/users at the database level.
    – nopol
    Jan 26, 2012 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.