CODEWITHSUNDEEP
sql-server-2008permissionsreadonlyproductionrole
trainer
trainer

Reputation: 379

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 ?

Upvotes: 0

Views: 1652

Answers (2)

Drew Miller
Drew Miller

Reputation: 675

The accepted answer is not quite correct - adding that role grants modify permissions, despite the name. This blog post covers how you can actually accomplish this task, at least with regards to jobs:

http://benchmarkitconsulting.com/colin-stasiuk/2009/01/21/creating-a-real-sqlagentreaderrole/

Upvotes: 1

trainer
trainer

Reputation: 379

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

Upvotes: 0

Related Questions