March 30, 2012 at 8:36 am
We'd like use the Policy Management to enforce the use of "dbo" as the schema owner when some of the "privileged" developers create stored procedures. I've tried using @name & @schema with various differences of "dbo" to no avail and different conditions. When I can get it to work, it works regardless if a "dbo" is used or not.
So for example if they create a proc called "GetValueProc" I'd like it to error out warning them they are in violation of the policy.
If they create a proc called "dbo.GetValueProc" it will get created with no issues.
Suggestions?
Thank you.
March 30, 2012 at 8:45 am
by default everything will be created in DBO unless one of two things, you explicitally give it a different schema or your default schema is different than dbo on the login.
to get what you want I would create a condition on the stored procedure facet using the schema property ensuring that you specify = DBO and have it to apply on change so that it is evaluated at procedure creation time
March 30, 2012 at 8:55 am
another way would be to create a custom DDL trigger for the CREATE_PROCEDURE event which reads in the XML details of the procedure creation and checks the schema XML node. This is what the sys policy will do, but if its custom you can modify it to do stuff like send mail, log to a table etc.
March 30, 2012 at 8:59 am
anthony.green (3/30/2012)
another way would be to create a custom DDL trigger for the CREATE_PROCEDURE event which reads in the XML details of the procedure creation and checks the schema XML node. This is what the sys policy will do, but if its custom you can modify it to do stuff like send mail, log to a table etc.
following the thread, because i'm curious if a policy can do that;
I was thinking the same way as anthony, that was the only wayyou could enforce a coding style like that would be a database level DDL trigger.
I'd like to be able to force fully qualified object names; dbo.Tablename throughout the script too, just to know it was possible, but that would requiring parsing it into tokens....
Lowell
March 30, 2012 at 9:08 am
anthony.green (3/30/2012)
by default everything will be created in DBO unless one of two things, you explicitally give it a different schema or your default schema is different than dbo on the login.to get what you want I would create a condition on the stored procedure facet using the schema property ensuring that you specify = DBO and have it to apply on change so that it is evaluated at procedure creation time
I tried that. This "sort of" works. It denies the stored proc from being written, but even if I change the condition it still doesn't work right.
Tried changing the 'dbo' to 'DBO' (per your suggestion) no dice. Also tried withou the tick marks & it wouldn't even compile.
I would like to avoid a DDL trigger but use that as my last resort. This has to be doable I would think.
April 2, 2012 at 1:02 am
created a condition and policy on my test machine and its working as intended using a schema = 'dbo', screen shot shows the violation
this is the script, just need to change Proc_dbo and Policy_proc_dbo to something meaningful for yourself
Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'Proc_dbo', @description=N'', @facet=N'StoredProcedure', @expression=N'<Operator>
<TypeClass>Bool</TypeClass>
<OpType>EQ</OpType>
<Count>2</Count>
<Attribute>
<TypeClass>String</TypeClass>
<Name>Schema</Name>
</Attribute>
<Constant>
<TypeClass>String</TypeClass>
<ObjType>System.String</ObjType>
<Value>dbo</Value>
</Constant>
</Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
Select @condition_id
GO
Declare @object_set_id int
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'policy_ObjectSet', @facet=N'StoredProcedure', @object_set_id=@object_set_id OUTPUT
Select @object_set_id
Declare @target_set_id int
EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'policy_ObjectSet', @type_skeleton=N'Server/Database/StoredProcedure', @type=N'PROCEDURE', @enabled=True, @target_set_id=@target_set_id OUTPUT
Select @target_set_id
EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/StoredProcedure', @level_name=N'StoredProcedure', @condition_name=N'', @target_set_level_id=0
EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0
GO
Declare @policy_id int
EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'policy_proc_dbo', @condition_name=N'Proc_dbo', @policy_category=N'', @description=N'', @help_text=N'', @help_link=N'', @schedule_uid=N'00000000-0000-0000-0000-000000000000', @execution_mode=1, @is_enabled=True, @policy_id=@policy_id OUTPUT, @root_condition_name=N'', @object_set=N'policy_ObjectSet'
Select @policy_id
GO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply