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
- Run
REVERTbefore switching to another user: successiveEXECUTE AScalls stack. - Since
GOis not T-SQL, the generated text is meant to be copied into a query window. - Account and group names are generic examples.