SQL Server Performance Forum – Threads Archive

Pemission Issue

Guys, I need your help. I have created a Role "DeveloperX" I have granted "Select" and "Execute" permission. Now Some of the stored procedures are inserting, updating and deleting data. Please tell me how to restrict this role to Select and Execute. This role should not be deleting, updating and inserting data through stored procedure even this role has execute permission. Ehsan Ehsan

When you granted execute permissions to a procedure, if the procedure is inserting/updating/deleting then role will get automatically insert/update/delete permissions… You don’t need to grant execlusive insert/delete/update permissions… When you don’t want the role have insert update delte permissions then why you are granting execute permission?
Remove the execute permission and deny insert, update, delete….
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thanks Mohammad. Ehsan

I believe it works bit different as compared to SQL 2000 and 2005, as you can control such access using user schemas. It would be better if you can confirm that bit of the question. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Satya, I am using SQL Server 2005. I know it works if I set permissions on Schemas. I was looking for to set permission on database level. Thanks Satya.
Ehsan Ehsan

Why not you can take help of catalog views in this case,http://msdn2.microsoft.com/en-us/library/ms191465.aspx fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

]]>