question

StuKay avatar image
StuKay asked

How to access any sys.database_principals view without sysadmin server role?

I have a requirement to monitor various aspects (users, roles etc) of all our SQL databases spread over a number of servers from one central database. The connections to other servers will be via a common Windows authenticated login and the requirement specifies this login can not have the sysadmin server role. I thought I would be able to achieve this via VIEW ANY DATABASE or VIEW ANY DEFINITION but apparently not. I do not want to have to set up specific permissions from within each database or use SSIS either. Having search around the only way I can think of doing this is to grant the login CONTROL SERVER and then revoke all of the roles/privileges it does not need. This seems a very long winded way of achieving the objective. Does anyone know a way of doing this?
rolescentral-management-server
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
Since you just need to select from the system view, I would think that [VIEW SERVER STATE][1] should do what you need without needing any other access. [1]: http://msdn.microsoft.com/en-us/library/ms188754.aspx
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I had tried that but have just checked again. It does not work. Note that to test this I am logged on to a server as the login and using the following: select * from ServerName.DBNamehbc.sys.database_principals It does work if the login is granted sysadmin or comtrol server.
0 Likes 0 ·
Checked that view in the Books Online. It says you would need to have ALTER ANY USER to query from it: http://msdn.microsoft.com/en-us/library/ms187328.aspx That's still less than SA or CONTROL SERVER.
0 Likes 0 ·
I did read that earlier and it did not work. Also tried it with GRANT ANY ROLE with the same result. This did throw me at the time. I think this will only work if the login is mapped as a user in the database.
0 Likes 0 ·
Haven't tested this one myself, so I'm stuck.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.