LLI Wiki — My Digital Knowledge Base

SQL Server: impersonation with EXECUTE AS USER

Switch user context with EXECUTE AS, check the identity and generate a permission script across all databases.

EXECUTE AS lets you temporarily impersonate another user in a SQL Server session, for example to test their permissions. REVERT restores the original context.

Switch context and check it

EXECUTE AS USER = 'CONTOSO\report_user';

SELECT SYSTEM_USER      --> CONTOSO\user.name
SELECT USER             --> dbo
SELECT CURRENT_USER     --> dbo
SELECT USER_NAME()      --> dbo
SELECT SESSION_USER     --> dbo

SELECT @@SPID
SELECT ORIGINAL_LOGIN()

REVERT

ORIGINAL_LOGIN() always returns the login that opened the connection, whatever EXECUTE AS is currently active.

Generate a permission script for every database

This example loops over all databases (except tempdb, model and msdb) and prints, with PRINT, the script that creates a user, adds it to db_owner and grants IMPERSONATE on that user to another principal. The script is printed, not executed.

EXECUTE AS USER = 'CONTOSO\admin_user'

DECLARE @database VARCHAR(2000);
DECLARE @sql VARCHAR(2000)
DECLARE cur_database CURSOR FOR
SELECT name FROM sys.databases WHERE name NOT IN ('tempdb','model','msdb')
OPEN  cur_database
FETCH cur_database INTO @database
WHILE (@@FETCH_STATUS = 0 )
BEGIN
    SET @sql = '
        USE '+QUOTENAME(@database)+'
        GO
        CREATE USER [CONTOSO\app_user] FOR LOGIN [CONTOSO\app_user]
        GO
        ALTER ROLE [db_owner] ADD MEMBER [CONTOSO\app_user]
        GO
        GRANT IMPERSONATE ON USER::[CONTOSO\app_user] TO [CONTOSO\SQL_Reporting]
        go'
   PRINT (@sql)
   FETCH cur_database INTO @database
END

/* if you need to try out a different user, run REVERT first */
REVERT

Notes

Mastodon LLIMastodon SocialFosstodon