How to define a server role that can see all that a sysadmin can see but cannot alter anything?

Question

How to define a server role that can see all that a sysadmin can see but cannot alter anything?

Yusuf Bhiwandiwala

Ten Centuries

Points: 1298
More actions
February 13, 2014 at 11:32 am

#304413

Hi,
In SQL Server 2008 is there a way a user can view everything (all databases, all logins, storage, et al) at server level and each database level, while being unable to perform any server level changes at least and even database level changes if possible?
Kind of like a "read-only sysadmin" role.
I am not a DBA but often times I have to review databases setup in production for completeness, verify all security, storage related stuff is configured properly and monitor performance etc.
DBAs can't give me sysadmin access.
Do I have any option?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

YB1D (2/13/2014)
Hi,
In SQL Server 2008 is there a way a user can view everything (all databases, all logins, storage, et al) at server level and each database level, while being unable to perform any server level changes at least and even database level changes if possible?
Kind of like a "read-only sysadmin" role.
I am not a DBA but often times I have to review databases setup in production for completeness, verify all security, storage related stuff is configured properly and monitor performance etc.
DBAs can't give me sysadmin access.
Do I have any option?

granting a login these two permissions is probably what you are after:

GRANT VIEW SERVER STATE TO [domain\lowell]

GRANT VIEW ANY DATABASE TO [domain\lowell]

that lets them see all the dmvs, as welll as the ddl and procedure definitions in any database; it does NOT let them see DATA inside any of the objects in any database, just the structure of the tables/views/procs themselves.

further, they cannot alter DDL either.

i think of it as granting read access to the sys schema on the server or any database.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Yusuf Bhiwandiwala Ten Centuries Points: 1298 More actions · Answer 2

granting a login these two permissions is probably what you are after:
GRANT VIEW SERVER STATE TO [domain\lowell]
GRANT VIEW ANY DATABASE TO [domain\lowell]

Thanks Lowell.

But that still does not give me view permissions to say, see all logins, or list all SQLAgent jobs and steps etc.

There is a lot much that sysadmin can see than the above permissions give view permission for.

Yusuf Bhiwandiwala Ten Centuries Points: 1298 More actions · Answer 3

Yusuf Bhiwandiwala

Ten Centuries

Points: 1298

February 17, 2014 at 10:51 pm

#1689733

Assume there is no way to achieve this?

Lowell SSC Guru Points: 323518 More actions · Answer 4

If you want thenlogin to see data then youd have to add a user for the login and grant it either specific select permissions or db_datareader.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!