September 17, 2008 at 7:46 am
Is there anyway to setup a trigger that fires when creating a stored procedure? When a developer adds a new stored procedure, I want to grant the ability to execute the new stored procedure to a system user. I know that the following query will retrieve all stored procedures in a database:
SELECT USER_NAME(uid) Owner, [name] StoredProcedure
FROM sysobjects
WHERE xtype = 'P'
If possible, I don't want to setup an automated scheduled task to assign permissions. Developers may create a stored procedure and expect it to be available to the system user "instantaneously". Likewise, developers may go days/weeks without creating new stored procedures.
September 17, 2008 at 7:58 am
You can create a trigger on sysobjects table, for INSERT when xType='P'.
September 17, 2008 at 8:10 am
Triggers on system objects were never safe... and they are 100% forbidden 2005 forward.
That's why DDL triggers were created.
I have never done that but I'm sure BOLs can point you in the right direction for that.
Let us know your final solution.
TIA.
September 17, 2008 at 8:23 am
Here's a simple example to get you started:
CREATE TRIGGER sProc_Trigger
ON DATABASE
FOR CREATE_PROCEDURE
AS
PRINT 'CREATE PROCEDURE Issued.'
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
--RAISERROR ('New tables cannot be created in this database.', 16, 1)
--ROLLBACK
SELECT EVENTDATA()
;
And here's a script to test it:
CREATE Proc Test_Proc
as
Select *
from sys.objects
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 17, 2008 at 1:49 pm
Here's my solution based on the following inputs:
1. rbarryyoung's replial @ 3:23PM
2. http://qa.sqlservercentral.com/articles/Security/sqlserversecuritythedb_executorrole/988/
Thanks again for everyone's help.
CREATE TRIGGER [Create_SP_Trigger]
ON DATABASE
FOR CREATE_PROCEDURE
AS
--PRINT 'CREATE PROCEDURE Issued.'
DECLARE @SQL nvarchar(4000),
@Owner sysname,
@StoredProcedure sysname,
@Return int
-- Returns the most recently inserted stored procedure
SELECT TOP (1) @Owner = USER_NAME(uid), @StoredProcedure = [name]
FROM sysobjects
WHERE xtype = 'P'
ORDER BY sysobjects.crdate DESC
-- Set the return code to 0
SET @Return = 0
-- Encapsulate the permissions assignment within a transaction
BEGIN TRAN
-- Create the SQL Statement. Since we're giving
-- access to all stored procedures, we have to
-- use a two-part naming convention to get the owner.
SET @SQL = 'GRANT EXECUTE ON [' + @Owner
+ '].[' + @StoredProcedure
+ '] TO db_executor';
-- Execute the SQL statement
EXEC @Return = sp_executesql @SQL
-- Check to see if there was an error.
IF (@Return = 0)
BEGIN
-- Exited fine, commit the permissions
COMMIT TRAN
END
ELSE
BEGIN
-- Exited with an error, rollback any changes
ROLLBACK TRAN
-- Report the error
SET @SQL = 'Error granting permission to ['
+ @Owner + '].[' + @StoredProcedure + ']';
RAISERROR(@SQL, 16, 1);
END
GO
September 17, 2008 at 11:22 pm
Glad I could help...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 31, 2008 at 12:25 pm
Thanks SSC and guys for the code, copy-and-paste worked
Did some testing code as well
No trigger
DISABLE TRIGGER Create_SP_Trigger ON DATABASE
GO
CREATE PROCEDURE Grant_Test_Proc
AS
PRINT 'should have triggered the trigger'
GO
EXECUTE AS USER = 'wglnapp'
EXEC Grant_Test_Proc
--The EXECUTE permission was denied on the object 'Grant_Test_Proc', database 'DBNAME', schema 'dbo'.
Trigger works
ENABLE TRIGGER Create_SP_Trigger ON DATABASE
GO
CREATE PROCEDURE Grant_Test_Proc
AS
PRINT 'should have triggered the trigger'
GO
EXECUTE AS USER = 'wglnapp'
EXEC Grant_Test_Proc
--should have triggered the trigger
Viewing the events that cause a trigger to fire
http://msdn.microsoft.com/en-us/library/ms189799.aspx
SELECT TE.*
FROM sys.trigger_events AS TE
JOIN sys.triggers AS T
ON T.object_id = TE.object_id
WHERE T.parent_class = 0
AND T.name = 'Create_SP_Trigger'
View SP's without db_executor EXECUTE permissions
SELECT USER_NAME(so.uid) Owner, so.[name] StoredProcedure
FROM sysobjects so
LEFT JOIN
(SELECT [id]
FROM sysprotects
WHERE uid = USER_ID('db_executor')
AND [action] = 224) sp
ON so.[id] = sp.[id]
WHERE so.xtype = 'P'
AND sp.[id] IS NULL
I just have a question, how do I switch back to dbo role? (I'm sysadmin on the server)
EXECUTE AS USER = 'dbo' -- select user_name()
DROP PROCEDURE Grant_Test_Proc
--Cannot execute as the database principal because the principal "dbo" does not exist,
this type of principal cannot be impersonated, or you do not have permission.
October 31, 2008 at 12:32 pm
Jerry Hung (10/31/2008)
I just have a question, how do I switch back to dbo role? (I'm sysadmin on the server)
Well, if you EXECUTE AS is a statement, then you use the REVERT command.
On the other hand, if your EXECUTE AS is a procedure clause, then you have to exit out of the procedure's context.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 31, 2008 at 12:38 pm
Awesome, REVERT worked (and another new command learned)
Thanks, RBarry
EXECUTE AS USER = 'wglnapp'
EXEC Grant_Test_Proc -- fired the trigger
GO
REVERT;
DROP PROCEDURE Grant_Test_Proc -- success
GO
October 31, 2008 at 1:29 pm
Great! Thanks for the feedback, Jerry.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply