Search code examples
sql-server-2008permissionsreadonlyproductionrole

SQL Server 2008 ReadOnly Role for Production Machine

I am a programmer temporarily tasked with the duties of a DBA at our firm. I need to create a User Id(or a role) that will have read-only access to EVERYTHING on the production SQL Server 2008.

This user should have read-only access to all DBs on this machine. That includes being able to view data, schema, scripts, stored procedures, functions, table design, linked servers, SQL Server Agent jobs, User IDs , Logins etc.

In short, EVERYTHING

However, it should NOT be able to MODIFY OR CREATE any of these.

I have managed to achieve most, but am unable to get read-only access to Linked servers, SQL Server Agent jobs, User IDs , Logins.

Is there any role(s) that will give me read-only access to EVERYTHING ?

Solution

  • Managed to get the ability to view all logins.

    GRANT VIEW ANY DEFINITION TO <Read-Only-User-Who-Sees-Everything>

    Managed to get the ability to view all jobs on SQL Server Agent: 

    EXEC sp_addrolemember 'SQLAgentReaderRole', '<ReadOnly-User-Who-Sees-Everything>'

    Managed to get ability to view properties of Linked Server. Well the properties window is still blank, but the following SQL statements do the trick for now. 

    select * from sys.linked_logins 
select * from sys.servers