April 21, 2011 at 5:07 pm
Greetings all 🙂
I am attempting to grant permissions for our report writer to create stored procedures however I believe I am not understanding 100% how to do this.
I created a test log in to validate this ability, only when I add the ALTER permission can I actually create a procedure. The problem with this (obviously) is that this grants the ability to create, alter, and drop a table as well as other objects. I can deny on the create table which prevents alter table, but it does not prevent the alteration of a table.
Can someone please tell me the only necessary permissions to create a procedure and nothing else? These procedures are necessary because they accept many user inputs as parameters and pass them through complex select statements. So the only real ability for this log in is to select stuff, execute procedures, and create procedures.
Any help is greatly appreciated!
Link to my blog http://notyelf.com/
April 21, 2011 at 7:23 pm
To start with, you have to grant CREATE PROCEDURE permissions. Looks like you've got that.
If it's in a schema the user doesn't own, you've got to grant ALTER SCHEMA permissions, too. But that means the user has the ability to do the other things you've mentioned.
The way we've solved this is by building a database-level DDL trigger that looks to see if the user is a db_owner or db_ddladmin role member. If not, and if the object in question is not a stored procedure, the operation is rolled back.
K. Brian Kelley
@kbriankelley
April 22, 2011 at 9:35 am
Great! Thank you for this information!
I was starting to come around to that line of thinking as well through my endeavors yesterday, and that makes complete sense. Thank you again!
Link to my blog http://notyelf.com/
February 10, 2012 at 2:17 pm
any chance to post this? im working my way through this as well, and want to see the logic; (did you have to list out all DDLs in the trigger?
CREATE TRIGGER sproc_only
ON DATABASE xyz
FOR DROP_TABLE, ALTER_TABLE...
or?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply