locked
"The database principal owns a symmetric key in the database, and cannot be dropped." RRS feed

  • Question

  • I am trying to drop a User from our sql 2008 DB and I get the above error message.

    How can I determine what symmetric key is owned by the user, change owner to dbo, and drop the user?

    TIA,

    edm2

    Thursday, July 31, 2014 8:38 PM

Answers

  • SELECT * FROM sys.symmetric_keys WHERE principal_id = user_id('nisse')

    ALTER AUTHORIZATION ON SYMMETRIC KEY::clef TO dbo

    Replace the placeholder names with the actual names.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by edm2 Thursday, July 31, 2014 9:51 PM
    Thursday, July 31, 2014 9:10 PM

All replies

  • SELECT * FROM sys.symmetric_keys WHERE principal_id = user_id('nisse')

    ALTER AUTHORIZATION ON SYMMETRIC KEY::clef TO dbo

    Replace the placeholder names with the actual names.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by edm2 Thursday, July 31, 2014 9:51 PM
    Thursday, July 31, 2014 9:10 PM
  • Thanks. That did the trick

    edm2

    Thursday, July 31, 2014 9:51 PM