Take the 2-minute tour ×
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.

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!

share|improve this question
add comment

2 Answers

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.

share|improve this answer
    
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 '12 at 14:32
add comment

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

share|improve this answer
    
I think the OP wants to do it with db principals. You're answer is the way he should be doing it though. +1 –  Thomas Stringer May 21 '12 at 0:17
    
I just realized this is an old post. Sorry for coming into the game late. :) –  Thomas Stringer May 21 '12 at 0:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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