SQL 2005 Read Only Database Object Permissions

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.
AaronRosemaAsked:
Who is Participating?
 
Chris MangusConnect With a Mentor Database AdministratorCommented:
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
 
Chris MangusDatabase AdministratorCommented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
AaronRosemaAuthor Commented:
There is no way to do this without also being able to grant permissions.
0
 
Chris MangusDatabase AdministratorCommented:
Disagree with poster.  It can be done and *is* done in many organizations.
0
 
AaronRosemaAuthor Commented:
Then do you know how to do it?  If not, I will just delete the question since I am not getting a solution.
0
 
Chris MangusDatabase AdministratorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.