June 24, 2009 at 8:40 pm
hi i m trying to create a DDL trigger.
when any user tries to create SP in any database,there should be grant statement included like
GRANT EXECUTE ON [dbo].[sp_abc] TO [custom_role]
GO
where custom_role =custom database level role in my environment.
I understand that Create Procedure is a seperate event and Grant statement is also seperate event.
But i can capture these events in EVENTDATA() but they are in XML form. how can I eveluate this EVENTDATA() and identify 2 seperate events? and if grant statement is not there then raiseerror an dont allow procedure to be created. I have implemented something like this but its not working...
CREATE TRIGGER [execute_trigger]
ON DATABASE for CREATE_PROCEDURE
AS
BEGIN
DECLARE @data XML;
SET @data = EVENTDATA();
IF (select charindex('custom_role',@data)) = 0
Begin
Raiserror ('Please include grant Execute permission to custom_role', 16,1)
Rollback
END
Else Print 'We are good to go'
END
It gives me this error
Implicit conversion from data type xml to varchar is not allowed. Use the CONVERT function to run this query.
1) first how to correct this error? or is there any other way to implemet the same requirement?
Thanks
July 20, 2009 at 3:26 am
If the role is supposed to have execute permissions on every stored procedure in the database then why not grant it a database-wide execute permission?
E.g.:
grantexecute
ondatabase::your_database_name_here
tocustom_role
Note, that a database-wide execute permission includes system objects.
However, assuming all user-defined stored procedures (and functions) in your database exist in user-defined schemas, you should limit the execute permissions to those schemas, thus limiting the permission to only include user-defined modules.
E.g.:
grantexecute
onschema::your_schema_name_here
tocustom_role
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply