locked
SQL 2008 R2 Error - Msg 208, Level 16, State 1, Procedure tr_dbSecurity, Line 5 RRS feed

  • Question

  • Hi I have been encountering with a strang error when i try to add a login to a Database on SQL 2008 R2 DAtabase Server.

    Even when i try change the dbowner to 'sa' i still get the same error.

    I am having sysadm privileges onthe server. I get these errors for only some databases but not all. I tried to restore a 2005 db onto 2008 R2 and i get this error but when i create a new database i dont have any of these errors.

    exec sp_changedbowner 'sa'

    Msg 208, Level 16, State 1, Procedure tr_dbSecurity, Line 5

    Invalid object name 'Security..EventData'.

     

    Greatly appreciate if you can please resolve this issue.

    Thursday, July 21, 2011 4:01 PM

Answers

  • There was a DDL Trigger which was enabled on the Restored Database which was trying to write to DBA Database which didnt exist on the Destination. I have disabled the Trigger and everything worked fine.

    Thanks for all your responses.


    krcsreddy
    Monday, July 25, 2011 10:09 PM

All replies

  • This is resolved
    Thursday, July 21, 2011 5:20 PM
  • This is resolved

    Would you mind providing the solution to your problem to assist others that may experience the same problem.
    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    Monday, July 25, 2011 3:05 AM
  • Could you please share your solution with others.

    Thanks in Advance.

    Velmurugan S


    velmurugan.s
    Monday, July 25, 2011 8:02 AM
  • On SS2008 i would use the below command ALTER AUTHORIZATION ON DATABASE::dbname to sa;
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, July 25, 2011 8:09 AM
  • On SS2008 i would use the below command ALTER AUTHORIZATION ON DATABASE::dbname to sa;
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, July 25, 2011 8:09 AM
  • > Would you mind providing the solution to your problem to assist others that may experience the same problem.

    It is highly unlikely that someone else would exactly this error message. Apparently there was a DDL trigger in place, and this trigger tried to write to a non-existing table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, July 25, 2011 9:09 AM
  • Really? Typing "SQL 2008 R2 Error - Msg 208, Level 16, State 1, Procedure tr_dbSecurity, Line 5" into Google produces numerous hits for this EXACT same error message. Obviously not as uncommon as you believe!


    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Monday, July 25, 2011 9:12 AM
  • > Really? Typing "SQL 2008 R2 Error - Msg 208, Level 16, State 1, Procedure tr_dbSecurity, Line 5" into Google produces numerous hits for this EXACT same error message. Obviously not as uncommon as you believe!

    That could be the case if there is a third-party product that spews out a bad DDL trigger.

    However, I got 9 hits, and all seemed to be related to this thread. And entering only tr_dbSecurity did not yield more hits.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, July 25, 2011 9:41 PM
  • There was a DDL Trigger which was enabled on the Restored Database which was trying to write to DBA Database which didnt exist on the Destination. I have disabled the Trigger and everything worked fine.

    Thanks for all your responses.


    krcsreddy
    Monday, July 25, 2011 10:09 PM