开发者

SQL Server 2008 ReadOnly Role for Production Machine

开发者 https://www.devze.com 2023-03-14 01:02 出处:网络
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.

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 
0

精彩评论

暂无评论...
验证码 换一张
取 消