EXECUTE AS USER ERROR!!!

  • Hi All,

    What server permission/ any permission do i need to have to run the

    EXECUTE AS USER

    For Example) when i execute the below query it is throwing me an error.

    The user "dom" has only "processadmin" at server level and few object permissions.

    what extra permission is require for 'dom' or the current logged in user/login i.e say "xyz".

    Note : "xyz" is also having same "processadmin" server role and few object privileges belong to other users.

    select System_user

    EXECUTE AS USER = 'dom;

    SELECT * FROM fn_my_permissions('SAMPLE', 'OBJECT')

    ORDER BY subentity_name, permission_name ;

    REVERT;

    GO

    /*

    Msg 15517, Level 16, State 1, Line 2

    Cannot execute as the database principal because the principal "esp_srm" does not exist, this type of principal cannot be impersonated, or you do not have permission.

    */

    select system_user

    Thanks in advance

  • mahesh when i read BOL, it has this statement:

    http://msdn.microsoft.com/en-us/library/ms188354.aspx

    what user are you logged in as before you try to execute as 'dom'? are you in as sa or a sysadmin via windows authentication?

    the way i read this from BOL:

    To specify a login_name or user_name that has implicit access to the database through a Windows group membership, you must have CONTROL permissions on the database.

    I'm not sure what role gives CONTROL, but I'm thinking it's gotta be more than db_owner...I looked at a couple of my databases,a nd my logins that have db_owner have CONNECT, but not CONTROL rights.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Hi,

    I have given "db_owner" database role to the current user and run the code. then it is working fine.

    But can i know any specific privilege to be given, instead of making him as "db_owner"?

    Thanks!

  • dbo or db_owner do have CONTROL rights and CONTROL, in my understanding, is essentially granting db_owner. You'd do this:

    GRANT CONTROL ON DATABASE::AdventureWorks TO username

  • Jack,

    Its working! 🙂

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply