September 16, 2014 at 12:21 pm
Hi SQL Enthusiasts,
Thanks for your time to look at my question.
In one of my environments, I need to grant the ability to view all the logins and agent jobs to an account, but I don't want to give him "sysadmin" or "securityadmin".
Have any of you done that before. Can you guide me.
Thanks!
Siva.
September 16, 2014 at 2:33 pm
What I would do is grant the user SELECT on the sys.server_principals table in the master database. This will allow the user to see all the logins.
I would then make the user a member of the SQLAgentReaderRole in the msdb database, this will allow the user to see all the jobs on the server.
September 17, 2014 at 2:54 pm
DBA From The Cold (9/16/2014)
What I would do is grant the user SELECT on the sys.server_principals table in the master database. This will allow the user to see all the logins.
This is not quite right. sys.server_principals is not a table, it is a catalog view to which everybody already has select permission on. By default you are only able to see your own login, system logins, and fixed server roles.
you could either grant ALTER ANY LOGIN which in this case is excessive, or you can grant view definition on all logins to the login you want to have access.
GRANT VIEW DEFINITION ON LOGIN::[loginname] TO [viewing_loginname]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply