Execute as login versus Execute as user

A handy feature on SQL Server is EXECUTE AS, this allows you to temporarily jump context to either another User or another Login, and then REVERT back once you’re done. Normally this is used in applications for allowing some functionality to run with higher than usual privileges, but it’s a great tool for debugging permission queries without changing your own settings.

Where people can get confused with this is with the differnce between User and Login contexts and why things work under one and not the other.

The key to remember is that User only references the database level user context. These means:

  • You can only ‘see’ things within the database context you switched as a ‘User’ only exists in one database. So no views, tables or sprocs in other dbs (so no access to linked server or system procedure in master,msdb,etc)
  • You will only get the database level permissions and roles, so no inheritance of Server level roles (sysadmin, dbcreator, etc)

Executing as Login means that you get the Instance wide permissions assigned to that Login. So if the login has rights across multiple DBs you can query them all, or execute system procedures.

So for a quick demo, let’s set up a little test environment. We’ll create a 2 databases called aasectest and absecttest, a SQL login call sectest, then grant it access into aasectest and absectest with a User testsec, and to give us something to select, a table in each DB:

USE [master]
GO

CREATE DATABASE [aasectest] 
go

CREATE DATABASE [absectest] 
go

CREATE LOGIN [sectest] WITH PASSWORD=N'', DEFAULT_DATABASE=[aasectest]
go

use [aasectest]
go

CREATE USER [testsec] FOR LOGIN [sectest] WITH DEFAULT_SCHEMA=[dbo]
GO

CREATE USER [testsec] FOR LOGIN [sectest] WITH DEFAULT_SCHEMA=[dbo]
GO

CREATE TABLE atable
(
col1 char(9)
)
GO
insert into atable (col1) values ('assectest')
GO

use [absectest]
GO

CREATE USER [testsec] FOR LOGIN [sectest] WITH DEFAULT_SCHEMA=[dbo]
GO

CREATE TABLE atable
(
col1 char(9)
)
GO
insert into atable (col1) values ('absectest')
GO

 

Now we’ll try some queries. First of all, with AS USER testsec:

use[aasectest]
go
EXECUTE AS USER = 'testsec'
GO
select col1 from aasectest.dbo.atable;
go
select col1 from absectest.dbo.atable;
go
use [absectest]
print 'switch database'
go
REVERT
GO
use [absectest]
go
EXECUTE AS USER = 'testsec'
GO
select col1 from aasectest.dbo.atable;
go
select col1 from absectest.dbo.atable;
go
REVERT
GO

The Messages returned being:

(1 row(s) affected)
Msg 916, Level 14, State 1, Line 1
The server principal "sectest" is not able to access the database "absectest" under the current security context.
Msg 916, Level 14, State 1, Line 1
The server principal "sectest" is not able to access the database "absectest" under the current security context.
swtich database
Msg 916, Level 14, State 1, Line 1
The server principal "sectest" is not able to access the database "aasectest" under the current security context.

(1 row(s) affected)

As can be seen, we can only see the data within the correct database for the user context we’re executing under.

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

No comments yet.

Leave a Reply