February 26, 2014 at 7:55 am
I am curious about his one.
I have a database that with a role (db_Execute) that has execute permissions on the stored procedures.
When my developers create a new stored procedure, they automatically do not inherit the role, which makes sense.
is there a way to automate granting "execute permissions" on newly created stored procedures?
I have tried database triggers using the EVENTDATA() as other blogs have suggested but it doesn't seem to work with SQL 2012 or I can't get it to work
Or am I stuck with manually granting permissions on every stored procedure created.
SQL Padre
aka Robert M Bishop
"Do or do not, there is no try" -- Yoda
February 26, 2014 at 9:17 am
Here is an example using a DDL trigger
CREATE TRIGGER procperm ON DATABASE
FOR CREATE_PROCEDURE
AS
DECLARE
@proc varchar(255),
@sql nvarchar(max);
SELECT @proc = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(max)') + '.' + EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)');
SET @sql = N'grant execute on ' + @proc + ' to db_Execute;';
PRINT @sql;
EXEC sp_executesql @sql;
CREATE PROC test2
AS
BEGIN
SELECT @@version;
END;
DROP PROC test2;
DROP TRIGGER procperm ON DATABASE;
you could also grant execute on the schema that procs are being created in.
GRANT EXECUTE ON SCHEMA::[dbo] TO [db_Execute]
February 26, 2014 at 9:22 am
Thank you sir! Granting EXECUTE on the schema worked perfectly (not a big fan of triggers).
AND it seems to inherit
SQL Padre
aka Robert M Bishop
"Do or do not, there is no try" -- Yoda
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply