Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

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?

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.
–
nopolJan 26 '12 at 14:32