August 12, 2015 at 4:24 am
What are the permissions needed to see server level properties?
We have a user who sent us a screen shot of the server properties window, which I was sure he wasn't able to see. I need to find out how he got these permissions.
EDIT: When I google this, I get Microsoft's "How to change server properties" pages and what permissions are needed to see database properties, but nothing on the permissions needed to see server properties.
August 12, 2015 at 4:27 am
VIEW SERVER STATE
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2015 at 5:20 am
Thank you.
Now I need to find out how the user got that permission.
August 12, 2015 at 6:03 am
Brandie Tarvin (8/12/2015)
Thank you.Now I need to find out how the user got that permission.
Oh, good grief. Apparently VIEW SERVER STATE is granted to anyone with the public role. In otherwords, anyone with permission to connect to the server.
SELECT * FROM sys.server_permissions sp
INNER JOIN sys.server_principals spr
ON sp.grantee_principal_id = spr.principal_id
WHERE sp.type = 'VWSS';
--locate specific server level permissions
Is this a default SQL Setting?
August 12, 2015 at 6:07 am
It is not. By default public has VIEW ANY DATABASE (which means they can see the names, not that they have access to them) and CONNECT on all endpoints. That's it.
Edit: in SQL 2012. I don't think it's different in 2008/2008 R2
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2015 at 6:09 am
GilaMonster (8/12/2015)
It is not. By default public has VIEW ANY DATABASE (which means they can see the names, not that they have access to them) and CONNECT on all endpoints. That's it.
Thank you. I'll have to look into this then.
August 12, 2015 at 6:37 am
GilaMonster (8/12/2015)
It is not. By default public has VIEW ANY DATABASE (which means they can see the names, not that they have access to them) and CONNECT on all endpoints. That's it.Edit: in SQL 2012. I don't think it's different in 2008/2008 R2
Just checked on my SQL2008R2 installs, and the only "accounts" with VIEW SERVER STATE are the ## replication and policy logins.
August 12, 2015 at 6:42 am
jasona.work (8/12/2015)
GilaMonster (8/12/2015)
It is not. By default public has VIEW ANY DATABASE (which means they can see the names, not that they have access to them) and CONNECT on all endpoints. That's it.Edit: in SQL 2012. I don't think it's different in 2008/2008 R2
Just checked on my SQL2008R2 installs, and the only "accounts" with VIEW SERVER STATE are the ## replication and policy logins.
Those have the permissions too. But I'm trying to figure out how most of our servers ended up with it added as a public permission. Corporate installed these servers, so I'm wondering if they did it or if, for some reason, we did it here back in the day. I'm waiting for my boss to get in so I can ask him.
Although... I wonder if they added it because they've got several audit jobs on the servers...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply