none
Policy not working as expected RRS feed

  • Question

  • Hello,

    I'm attempting to build a policy to stop the removal of a specific login from a SQL Server 2008 R2 instance that has been given to a client. The purpose of the login is for me so that I can always have a login to the server should they mess things up and I don't want them deleting it. However my first attempt at the policy doesn't appear to work as I am still able to delete the login. I've built the policy as follows:

    Declare @object_set_id int
    EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'BSDApps must have access_ObjectSet', @facet=N'Login', @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'BSDApps must have access_ObjectSet', @type_skeleton=N'Server/Login', @type=N'LOGIN', @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/Login', @level_name=N'Login', @condition_name=N'BSDApps Login', @target_set_level_id=0
    
    
    GO
    
    Declare @policy_id int
    EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'BSDApps must have access', @condition_name=N'Stop removal of BSDApps', @policy_category=N'', @description=N'', @help_text=N'', @help_link=N'', @schedule_uid=N'00000000-0000-0000-0000-000000000000', @execution_mode=0, @is_enabled=False, @policy_id=@policy_id OUTPUT, @root_condition_name=N'', @object_set=N'BSDApps must have access_ObjectSet'
    Select @policy_id
    
    
    GO
    
    

    This is based on two conditions which are:

    Declare @condition_id int
    EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'Stop removal of BSDApps', @description=N'', @facet=N'Login', @expression=N'<Operator>
     <TypeClass>Bool</TypeClass>
     <OpType>EQ</OpType>
     <Count>2</Count>
     <Attribute>
      <TypeClass>Bool</TypeClass>
      <Name>HasAccess</Name>
     </Attribute>
     <Function>
      <TypeClass>Bool</TypeClass>
      <FunctionType>True</FunctionType>
      <ReturnType>Bool</ReturnType>
      <Count>0</Count>
     </Function>
    </Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
    Select @condition_id
    
    GO
    
    Declare @condition_id int
    EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'BSDApps Login', @description=N'', @facet=N'Login', @expression=N'<Operator>
     <TypeClass>Bool</TypeClass>
     <OpType>EQ</OpType>
     <Count>2</Count>
     <Attribute>
      <TypeClass>String</TypeClass>
      <Name>Name</Name>
     </Attribute>
     <Constant>
      <TypeClass>String</TypeClass>
      <ObjType>System.String</ObjType>
      <Value>BSDApps</Value>
     </Constant>
    </Operator>', @is_name_condition=1, @obj_name=N'BSDApps', @condition_id=@condition_id OUTPUT
    Select @condition_id
    
    GO
    
    Because I can still delete the login I presume that the policy is not working. What have I done wrong or am I going about this the wrong way?

    Thanks,

    Mike

    Thursday, July 14, 2011 9:08 PM

Answers

  • Hi Mike,

    The policy above can evaluate all existing logins to check their Name and HasAccess property, but will not prevent a specific login from dropped. To prevent a login from dropping, you can remove delete permission on this login from privileged users, or you may document that this login should be not dropped to the DBA teams.


    Best Regards
    Alex Feng | Forum Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    • Marked as answer by renMike Saturday, July 16, 2011 12:17 PM
    Friday, July 15, 2011 3:05 AM
    Moderator

All replies

  • Hi Mike,

    The policy above can evaluate all existing logins to check their Name and HasAccess property, but will not prevent a specific login from dropped. To prevent a login from dropping, you can remove delete permission on this login from privileged users, or you may document that this login should be not dropped to the DBA teams.


    Best Regards
    Alex Feng | Forum Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    • Marked as answer by renMike Saturday, July 16, 2011 12:17 PM
    Friday, July 15, 2011 3:05 AM
    Moderator
  • Alex,

    Ah, ok. My misunderstanding. Thanks for clarifying that the policy will not be able to do what I had hoped and for suggesting the correct approach.

    I am however now stuck as I am not able to locate the 'delete permission' on the login I don't want to be dropped. Is it against my login or against the end users login?

    Unfortunatley simply documenting that my login should not be dropped is not sufficient since the box is going out of my control for the majority of its life (probably up to the point where they mess it up and require halp!).

    Thanks,

    Mike

    Friday, July 15, 2011 8:21 AM