locked
Importing Policy in SQL Server RRS feed

  • Question

  • Already i created one thread for this query but i didn't get any response. Hence i am creating this thread, Please help me.

    Hi,

    I have imported policies in sql server 2012 under. But i can't see the policies. under Policy Management\Policies.

    Then when i try to import again, it showing the already exist. Even i can find policies under G:\SQLServer2012(X86)\110\Tools\Policies\DatabaseEngine\1033

    Please help me what to do?


    Tuesday, May 5, 2015 3:46 PM

Answers

  • When replacing existing policy (selected Checkbox option) getting error in log like policy already existed.

    Please go through the screen shot.

    If this is the error you got then for sure object set exists: 

    select * from msdb.[dbo].[syspolicy_object_sets_internal] where object_set_name = 'Database Collation_ObjectSet'

    Delete as I suggested and try again it should work.

    • Marked as answer by VijayKSQL Wednesday, May 13, 2015 3:52 AM
    Friday, May 8, 2015 2:52 AM

All replies

  • Hi,

    I have imported policies in sql server 2012 under. But i can't see the policies. under Policy Management\Policies.

    Then when i try to import again, it showing the already exist. Even i can find policies under G:\SQLServer2012(X86)\110\Tools\Policies\DatabaseEngine\1033

    Please help me what to do?

    Monday, May 4, 2015 3:07 PM
  • Did you try with refresh policies folder after import :)

    Also user permission requires membership in the PolicyAdministratorRole role in the msdb database.

    Monday, May 4, 2015 4:26 PM
  • try refreshing like Dave suggested above and also, to make sure it really was imported, do you see the policy name being returned after running this query...

    select * from msdb.[dbo].[syspolicy_policies_internal]
    where is_system=0


    Hope it Helps!!

    Wednesday, May 6, 2015 10:08 AM
  • While running above query not getting anything. But when i try to import policy getting error like 'already exists'.

    Please help me.

    Wednesday, May 6, 2015 3:58 PM
  • While running above query not getting anything. But when i try to import policy getting error like 'already exists'.

    Please help me.

    ok. what does this return . it appears you are creating a policy with already existing name or refresh the  policy like Dave said. do you see the policy in the output
    select * from msdb.[dbo].[syspolicy_policies_internal]

    Hope it Helps!!

    Wednesday, May 6, 2015 4:15 PM
  • select * from msdb.[dbo].[syspolicy_policies_internal]

    Results: Empty Output.

    Then i refreshed as shown above and closed management studio and reconnected.

    Again i try to started the import same policies (from backup) but getting error like already exists.

    IF already existed it should show if not existed it should import.

    I don't know whats happening.

    Wednesday, May 6, 2015 5:12 PM
  • select * from msdb.[dbo].[syspolicy_policies_internal]

    Results: Empty Output.

    Then i refreshed as shown above and closed management studio and reconnected.

    Again i try to started the import same policies (from backup) but getting error like already exists.

    IF already existed it should show if not existed it should import.

    I don't know whats happening.

    what happens when you replace the existing policy - while you import policy - you should see a checkbox option to overwrite the policy if it is duplicate.. check it and try...

    if the above query did not return anything- it means you have no policies in PBM ...


    Hope it Helps!!

    Wednesday, May 6, 2015 5:24 PM
  • When replacing existing policy (selected Checkbox option) getting error in log like policy already existed.

    Please go through the screen shot.

    Wednesday, May 6, 2015 7:43 PM
  • It is not saying that the policy already exists. It says that the ObjectSet Database Collation ObjectSet already exists. Whatever that means.

    I note that when I try to import a policy that already exists, it simply says "Policy xxx already exists".

    Are the policies you are trying to import related to collations?

    On which version of SQL Server were these policies created? Which are you trying to import it on?

    Any chance you can upload the policy file somewhere, so that we can play with it?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, May 6, 2015 9:10 PM
  • Microsoft SQL Server 2012 - 11.0.5058.0 (X64) Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

    Trying to importing from same version and edition.

    Thursday, May 7, 2015 1:35 PM
  • Looks like you already have this policy. You can confirm that by using below command:

    select * from msdb.[dbo].[syspolicy_policies_internal]

    or the object set:

    select * from msdb.[dbo].[syspolicy_object_sets_internal]

    In your case looks like you are trying to import Database Collation.xml. Try to delete policy(Database Collation) using below command and import again.

     

    -- Delete the policy
    Declare @Object_set_ID_Delete INT
    set @Object_set_ID_Delete = (select object_set_id from msdb.dbo.syspolicy_policies_internal where name = 'Database Collation')

    EXEC msdb.dbo.sp_syspolicy_delete_policy @name= 'Database Collation'
    EXEC msdb.dbo.sp_syspolicy_delete_object_set @object_set_id = @Object_set_ID_Delete
    GO

    EXEC msdb.dbo.sp_syspolicy_delete_condition @name = 'Database Collation'



    • Edited by dave_gona Thursday, May 7, 2015 3:03 PM
    Thursday, May 7, 2015 2:50 PM
  • When i use

    select object_set_id from msdb.dbo.syspolicy_policies_internal where name = 'Database Collation' against MSDB

    i didn't get anything, i mean empty results.

     
    Thursday, May 7, 2015 4:58 PM
  • try on this:

    select * from msdb.[dbo].[syspolicy_object_sets_internal] where object_set_name = 'Database Collation_ObjectSet'

    Its definitely there.


    • Edited by dave_gona Thursday, May 7, 2015 5:43 PM
    Thursday, May 7, 2015 5:35 PM
  • When replacing existing policy (selected Checkbox option) getting error in log like policy already existed.

    Please go through the screen shot.

    If this is the error you got then for sure object set exists: 

    select * from msdb.[dbo].[syspolicy_object_sets_internal] where object_set_name = 'Database Collation_ObjectSet'

    Delete as I suggested and try again it should work.

    • Marked as answer by VijayKSQL Wednesday, May 13, 2015 3:52 AM
    Friday, May 8, 2015 2:52 AM
  • Actually this is production box. i can't take risk.

    Anyway i will search for any alernative.

    Thank you very much.

    Monday, May 11, 2015 1:21 PM