If you find it, show them

I was tooling around in SSMS the other day. In this particular instance I was granting a login instance-level permissions to test out a third party application at work. I did not want to give it sysadmin privileges for this so I just started out with giving it VIEW SERVER STATE (which most DMVs require in order to query them) and VIEW ANY DATABASE. [If you have never heard of these permissions, check out this little tidbit of info here from Brian Kelley (b|t).)

Wait…go and actually read the tidbit of info from Brian...

Ok now that you have read that, you saw the T-SQL code to find the server-level permissions granted to any account on an instance of SQL Server 2005 or 2008. Well what if I only wanted it for one login and I wanted to use the GUI? Ah, ha!!!

So on my test system at home I have [myUser], with the current permissions showing below (retrieved using Brian’s T-SQL code).

Now I am going to grant the account VIEW SERVER STATE and VIEW ANY DATABASE. Running the T-SQL code again will show you this:

Now how can you see this using SSMS, you ask? …I’m glad you asked. Open up SSMS and go to the properties of your login you are interested in, click on “Securables” in the left pane. You should see this window:

Now click on the “Search” button. You are presented with options as to what objects you want SSMS to search for, I chose “The server…”. You can play around later to see what the options do if you like, these selections are not remembered so once you close the window they go away. After clicking OK you will see your instance name show up under Securables and the bottom pain will show all the instance level permissions. I scroll all the way to the bottom and will see the permissions I assigned to my login:

That is all for now…

Advertisement