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, script开发者_高级运维s, 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 ?
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/
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
精彩评论