Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to define a server role that can see all that a sysadmin can see but cannot alter anything? Expand / Collapse
Author
Message
Posted Thursday, February 13, 2014 11:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 48, Visits: 315
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?
Post #1541353
Posted Thursday, February 13, 2014 1:34 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:16 AM
Points: 12,749, Visits: 31,115
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1541399
Posted Thursday, February 13, 2014 10:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 48, Visits: 315

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.

Post #1541471
Posted Monday, February 17, 2014 10:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 48, Visits: 315
Assume there is no way to achieve this?
Post #1542370
Posted Tuesday, February 18, 2014 3:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:16 AM
Points: 12,749, Visits: 31,115
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1542437
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse