Server-wide read permissions by AussieJohn

Any chance of creating server-wide read permissions?

I’m trying to set up, in SQL Server 2012, a user-defined server role to give DBAs read access to everything on the server. The idea of the role is to provide safe access for DBAs so they can browse everything but update nothing. The sort of role you could give to a new DBA to investigate things without worrying about the DBA dropping or updating something.
In a sense what I’m looking for is a server-level version of the database db_datareader role. I want to be able to see the data in tables and SQL Server metadata such as table definitions, DMVs and SQL Agent job details.

I started off thinking that a user-defined server role (e.g. “DBA_Read”) using the VIEW ANY DEFINITION server permission would do the trick for metadata, but it’s much more complicated than this in SQL Server 2012. To see DMVs you also need to add server-level permission VIEW SERVER STATE. To see database object metadata you need to grant (minimal) access to the database – CREATE USER in every database. To see SQL Agent job information you need to grant SQLAgentReader role in msdb. These last two stages require database level access but (as far as I can see) you can’t use a server level role as the target of this database access (so you can’t do it via the new user-defined server role).
At this point (and we haven’t yet granted SELECT access to the data) you’re starting to think “let’s just carry on using ‘sysadmin’ access, it’s much easier”.

To summarise, what I would like are new server level permissions such as
• VIEW ANY METADATA (including catalog views, DMVs, msdb metadata)
• VIEW ANY DATA (in any database, except where a DENY over-rides)
which would behave a bit like the sysadmin role but with any CONTROL permissions removed. The sysadmin thing is important – you want to grant the access server-wide to a server role. You don’t want to be having to add database level access in order to get this server-wide access to work.

[Ideally I’d like to be able to take this even further, and have VIEW access to SSIS, SSRS and SSAS, as well as the RDBMS.]

The benefits would be
• effective DBA read-only access (for checking SQL Server)
• reduced need for sysadmin role (or CONTROL SERVER) access
• better audit compliance.

[This request is similar to
378050 October 2008 Simon Sabin
288797 July 2007     Prasad]