locked
Cannot execute as the database principal because the principal "dbo" does not exist RRS feed

  • Question

  • I have SQL user, which should able to update table data and also to truncate table.
    User should have min permissions as possible.

    Current database memberhip are followings: (not enough for truncate)
    - db_datareader
    - db_datawriter

    I got tip that I should make stored procedure:

    CREATE PROC DoTruncate
    WITH EXECUTE AS OWNER
    AS
     Truncate table [databasename].[dbo].[test]
    GO

    (succesful)

    grant execute on DoTruncate to username

    (succesful)

    Exec DoTruncate

    Msg 15517, Level 16, State 1, Procedure DoTruncate, Line 0
    Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.


    Kenny_I

    Thursday, May 16, 2013 6:24 AM

Answers

  • I would suspect that the problem is that the database has been restored/attached from a different instance, and the database owner according to the database does not match the master database. Specifically, the owner according to the database does not exist on the server. Change the owner of the database forth and back, and it would sort out.

    There is a second way to achieve this, which I think is better than EXECUTE AS, and that signing the procedure with a certificate, and then grant a user created from that certificate ALTER permission on the table.

    I have a longer article on my web site that discusses both techniques in detail: http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kenny_I Friday, May 17, 2013 5:44 AM
    Thursday, May 16, 2013 11:47 AM

All replies

  • Hi,

    Give the permission  db_owner to user.

    if you getting any problem , try this one

    GRANT CONTROL ON DATABASE::AdventureWorks TO username


    Please mark the post as answer if it is helpfull to you

    Thursday, May 16, 2013 8:53 AM
  • Wow! That is pretty radical, opening up everything on the database to the user. Not something that I recommend...

    My guess is that the problem is the 3-part naming of the table to be truncated. Is the table in the same db as the proc? If so, try removing the database name.


    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, May 16, 2013 10:58 AM
  • I would suspect that the problem is that the database has been restored/attached from a different instance, and the database owner according to the database does not match the master database. Specifically, the owner according to the database does not exist on the server. Change the owner of the database forth and back, and it would sort out.

    There is a second way to achieve this, which I think is better than EXECUTE AS, and that signing the procedure with a certificate, and then grant a user created from that certificate ALTER permission on the table.

    I have a longer article on my web site that discusses both techniques in detail: http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kenny_I Friday, May 17, 2013 5:44 AM
    Thursday, May 16, 2013 11:47 AM
  • Right, check out the database owner and fix that first using sp_changedbowner.

    Try this - http://support.microsoft.com/kb/913423

    Thursday, May 16, 2013 11:54 PM