Nick Grattan's Blog

About Microsoft SharePoint, .NET, Natural Language Processing and Machine Learning

Bringing Impersonation to SQL Server 2005

with 6 comments

Frequently I find that I would like to execute TSQL commands under the context of another login, for example, when testing a database user’s permissions on objects. The SQL Server 2005 EXECUTE AS statement allows us to do this. To show how this works, first create a login and a user for the AdventureWorks database and test the current login and user name:

 

CREATE LOGIN OrdinaryLogin WITH PASSWORD = ‘Password’

GO

CREATE USER OrdinaryUser FOR LOGIN OrdinaryLogin

GO

SELECT SUSER_NAME(), USER_NAME()

 

This reports ‘W2003\Administrator’ and ‘dbo’ for me. Now, impersonate the login that’s just been created and test the current login and user name again:

 

EXECUTE AS LOGIN = ‘OrdinaryLogin’;

SELECT SUSER_NAME(), USER_NAME()

 

This now reports ‘OrdinaryLogin’ and ‘OrdinaryUser’. TSQL commands can now be executed in the security context of ‘OrdinaryUser’. Finally, you can revert back to the previous login by executing this statement:

 

REVERT

 

When using EXECUTE AS the current user must have IMPERSONATE permissions on the target login; ‘dbo’ already has this permission for all database users but the permission can be granted to other users:

 

GRANT IMPERSONATE ON USER:: OrdinaryUser TO AnotherUser

 

This now allows ‘OrdinaryUser’ to impersonate ‘AnotherUser’. SQL Server 2005 also provides the SETUSER to perform a similar task, but this can only be executed by sysadmin/db_owner roles members.

Advertisements

Written by Nick Grattan

July 24, 2007 at 7:30 am

Posted in Uncategorized

6 Responses

Subscribe to comments with RSS.

  1. Any clues on how could it work if I need to use windows authentication?

    Rauf

    October 18, 2009 at 2:21 pm

  2. Rauf, Try creating the login with the following command:

    CREATE LOGIN [\] FROM WINDOWS

    This will allow you to create the login assoicated with a Windows login. Regards, Nick.

    Nick Grattan

    October 19, 2009 at 3:06 pm

  3. I do not understand why the REVERT command does not work. Here is what I do: I am logged in as win authenticated user, then i impersonate some other login with very low access, and when I try to execute REVERT, the command is successful, but SELECT SUSER_NAME(), USER_NAME() is still giving me the impersonated user.

    Feodor

    January 19, 2010 at 9:50 pm

  4. Thanks – still a useful blog post :-)!!!

    daftspaniel

    March 14, 2011 at 4:21 pm

  5. […] Frequently I find that I would like to execute TSQL commands under the context of another login, for example, when testing a database user’s permissions on objects. The SQL Server 2005 EXECUTE AS statement allows us to do this. To show how this works, first create a login and a user for the AdventureWorks database and test the current login and user name:   CREATE LOGIN OrdinaryLogin WITH PASSWORD = 'Password' GO CREATE USER OrdinaryUs … Read More […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: