Execute As in SQL (Similar to Runas in Windows context)

When we need to execute any SQL statement with other user context we have to use EXECUTE AS statement to impersonate other user access.

Impersonation can be done as different login or different user.This is similar to RunAs in windows context

 

Example:


use AdventureWorks2014

-- How to provide Impersonate permissions
GRANT IMPERSONATE ON USER:: vijaysql TO [ais-vijay\Vijay]

-- Check context
SELECT SUSER_NAME(), USER_NAME();

-- Execute as Login
EXECUTE AS login = 'vijaysql'
-- Check context
SELECT SUSER_NAME(), USER_NAME();

-- Reverting back the context
REVERT
-- Check context
SELECT SUSER_NAME(), USER_NAME();

-- Execute as USer
EXECUTE AS user = 'vijaysql'
-- Check context
SELECT SUSER_NAME(), USER_NAME();

-- Reverting back the context
REVERT

Reference:

https://msdn.microsoft.com/en-us/library/ms181362.aspx
https://msdn.microsoft.com/en-us/library/ms188354.aspx?f=255&MSPPError=-2147217396

Advertisements
This entry was posted in Referene, SQL, SQL Query, Uncategorized and tagged , , , . Bookmark the permalink.

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