Solved

SQL 2005 Read Only Database Object Permissions

Posted on 2010-09-08
7
356 Views
Last Modified: 2012-05-10
I am looking for a way to allow my developers to be able to read table, stored procedure, view and function permissions without them being able to change them.  

I am thinking that I need to give them access to some of the system views, but I do not know which ones are needed for this.  db_securityadmin allows them to change permissions which they should not be able to do.
0
Comment
Question by:AaronRosema
  • 4
  • 2
7 Comments
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 33631947
I don't know the exact system tables you need off the top of my head, but, I would create a custom view into the appropriate system table and grant your developers SELECT permission on the view you create.
0
 
LVL 4

Expert Comment

by:MichaelMH
ID: 33635444
0
 

Author Comment

by:AaronRosema
ID: 33730955
There is no way to do this without also being able to grant permissions.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:Chris Mangus
ID: 33733956
Disagree with poster.  It can be done and *is* done in many organizations.
0
 

Author Comment

by:AaronRosema
ID: 33734636
Then do you know how to do it?  If not, I will just delete the question since I am not getting a solution.
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 33735329
I gave you enough info to be able to figure it out.  If you're unable to figure it out then I will create a view and send you the code.  You will need to be able to give SELECT permission to your developer group to the view.
0
 
LVL 17

Accepted Solution

by:
Chris Mangus earned 500 total points
ID: 33735626
Here's the code for a view.  Give your developers SELECT permission to the view you create.
SELECT su.name, OBJECT_NAME(sp.ID) As Object
		, CASE 
			WHEN sps.Action = 26	THEN 'REFERENCE'
			WHEN sps.Action = 178	THEN 'CREATE FUNCTION'
			WHEN sps.Action = 193	THEN 'SELECT'	
			WHEN sps.Action = 195	THEN 'INSERT'
			WHEN sps.Action = 196	THEN 'DELETE'
			WHEN sps.Action = 197	THEN 'UPDATE'
			WHEN sps.Action = 198	THEN 'CREATE TABLE'
			WHEN sps.Action = 203	THEN 'CREATE DATABASE'
			WHEN sps.Action = 207	THEN 'CREATE VIEW'
			WHEN sps.Action = 222 	THEN 'CREATE PROCEDURE'
			WHEN sps.Action = 224	THEN 'EXECUTE'
			WHEN sps.Action = 228	THEN 'BACKUP DATABASE'
			WHEN sps.Action = 233	THEN 'CREATE DEFAULT'
			WHEN sps.Action = 235	THEN 'BACKUP LOG'
			WHEN sps.Action = 236	THEN 'CREATE RULE'
			ELSE 'UNKNOWN'
		  END As Permission
  FROM	 syspermissions sp
 INNER JOIN sysusers su
	ON sp.grantee = su.uid
 INNER JOIN sysprotects sps
	ON su.uid = sps.uid AND sp.id = sps.id
Order By su.name

Open in new window

0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql help 5 55
Caste datetime 2 69
Update a summary table with values from detail records 6 34
Email Notifications for SQL 2005 9 36
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question