Post reply

EXECUTE AS USER ERROR!!!

  • December 28, 2009 at 6:11 am

    #386014

    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

  • December 28, 2009 at 6:29 am

    #1096651

    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!

  • December 28, 2009 at 6:48 am

    #1096656

    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!

  • December 28, 2009 at 9:41 am

    #1096741

    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 Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • December 29, 2009 at 12:24 am

    #1096921

    Jack,

    Its working! 🙂

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

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