Procedure with Execute as login?

Sometimes we need a low-privileged user to do a specific administration task or task that require some server-level permissions (such as VIEW SERVER STATE, ALTER TRACE etc). Of course, we do not want to give that account server-level privilege, because it would not comply to the “least possible set of privileges” security recommendation. Sometimes we need a finer grained server-level permission, or to limit server-level permission to certain databases for example. That is advanced security task for a DBA, and I will show you here how to do it through an example.

Custom sp_who2

We will build a procedure that calls sp_who2, let low-privileged user to execute it and see all the processes on the server, without giving the VIEW SERVER STATE to that low-privileged account, as that privilege would give the user much broader rights than just calling sp_who2. The problem is that procedure is by default executed as caller, and the low-privileged caller of sp_who2 can’t see any sessions except his own. Let’s try:

-- We will create a database and a procedure which calls sp_who2.
-- We will also create a low-privileged login and give him exec permission on that procedure.

create database TestCert
create login LowPrivLogin with password='Str0ngPWD!'
GO
use TestCert
create user LowPrivUser for login LowPrivLogin
GO
create procedure dbo.show_processes
--with execute as 'dbo' -- even this wont help, because dbo is db user and can receive only privileges at db level, not server level
as
begin
	select database_user = USER_NAME(), [login] = SUSER_SNAME(), [original login]=ORIGINAL_LOGIN()
	select * from sys.login_token -- who am i
	select * from sys.fn_my_permissions(null,'server') -- what server rights do i have
	exec sys.sp_who2
end
GO
grant exec on dbo.show_processes to LowPrivUser
GO

execute as login='LowPrivLogin'
exec sys.sp_who2 -- shows only my session, not good
exec dbo.show_processes -- shows only my session, not good
revert
GO

-- We will create a database and a procedure which calls sp_who2.

-- We will also create a low-privileged login and give him exec permission on that procedure.

create database TestCert

create login LowPrivLogin with password='Str0ngPWD!'

use TestCert

create user LowPrivUser for login LowPrivLogin

create procedure dbo.show_processes

--with execute as 'dbo' -- even this wont help, because dbo is db user and can receive only privileges at db level, not server level

begin

select database_user = USER_NAME(), [login] = SUSER_SNAME(), [original login]=ORIGINAL_LOGIN()

select * from sys.login_token -- who am i

select * from sys.fn_my_permissions(null,'server') -- what server rights do i have

exec sys.sp_who2

end

grant exec on dbo.show_processes to LowPrivUser

execute as login='LowPrivLogin'

exec sys.sp_who2 -- shows only my session, not good

exec dbo.show_processes -- shows only my session, not good

revert

Output of the procedure is below:

The first recordset shows the database user, impersonated login, and my real (original) login. Second recordset (sys.login_token) shows we have LowPrivLogin and public server role context execution tokens. Third recordset shows that we have CONNECT SQL and VIEW ANY DATABASE server privileges we received from the public role. Fourth recordset is the output of sp_who2: only one row is there, our session, because we do not have VIEW DATABASE STATE on any database, and we certainly do not have VIEW SERVER STATE to view them all.

Even if we uncomment “WITH EXECUTE AS ‘dbo'” (or SELF, or OWNER, or any other user) the sp_who2 inside a procedure won’t give us all the sessions on the server.

Why “WITH EXECUTE AS ‘dbo'” won’t help?

In batch statements we can use:

EXECUTE AS USER = 'SomeDbUser'
EXECUTE schema.procedure
REVERT

EXECUTE AS USER = 'SomeDbUser'

EXECUTE schema.procedure

REVERT

The problem is that database user can only receive database-level privileges. VIEW SERVER STATE is a server-level privilege and cannot be assigned to a database user. Server-level privilege can only be assigned to a server-level principal: login.

EXECUTE AS LOGIN = 'SomeLogin'
EXECUTE schema.procedure
REVERT

EXECUTE AS LOGIN = 'SomeLogin'

EXECUTE schema.procedure

REVERT

That is promising!

When creating a procedure, function, DML trigger, or database-scoped DDL trigger there is “WITH EXECUTE AS <something>” clause available. But, unfortunately, that clause only accepts database users, not logins! SELF and OWNER are also resolved to a certain database user: SELF is resolved to a db user that executed a “CREATE PROCEDURE” statement, and OWNER is resolved to a db user that owns a procedure. Even “dbo”, a special database user whose rights are not checked at all, is scoped to a database and can’t receive a server-level privilege. Only server-scoped triggers can have “with execute as <login>” clause: server-level DDL triggers and logon triggers.

Since we can’t define a procedure to use “WITH EXECUTE AS <login>”, our plan to create a procedure to execute under server-level privilege is doomed to a failure! Or is it?

Procedure “WITH EXECUTE AS <login>” is possible ?

Not really, not without the tricks. And the “trick” is in signing the procedure. Signing the procedure with a certificate makes it executing under login associated with that certificate. Certificate is like a glue that connects the procedure to execute under specific login. That login is a special kind of login called “LOGIN mapped to a CERTIFICATE”. Nobody can’t actually log-in with that login. It servers just to receive a server-level permissions. And the code (procedure) can run under that login if it is signed by that login’s certificate. Sounds complicated? And it is! But once you understand how it works, it’s not so hard.

Use certificate or asymmetric key?

We cannot sign the procedure with symmetric key. That leaves us to choose between certificate and asymmetric key. Certificate is asymmetric key with some metadata added, like subject and expiration date. But the most important difference for us is that certificate can be backed-up and restored separately from database. Asymmetric key can only be backed-up and restored with a database backup/restore he lives in. We will need the same certificate to be in two databases:

master – because certificate mapped to a login can only be in master database
our target database – we can sign a procedure only with a certificate that is in the same database as the procedure

Because we need to copy the same certificate to another database, it is much easier to do it with certificate. With asymmetric key we could probably restore the whole database over another database, and recreate all the objects and data previously exported, or mess with updating system tables which is not supported and of course, not recommended.

Get our hands dirty!

First, we will create a certificate in the master database, and create a login mapped to that certificate. Then copy that certificate from master to our database via backup/restore of the certificate. Then we will sign our procedure with that certificate.

-- Create login to which we will grant view server state. Login will be mapped to certificate.
-- Sign procedure with that certificate - meaning procedure will execute under that high-priv login.
-- certificate must be in master database in order to be mapped to login

use master -- create certificate in master database

create certificate HighPrivCert
ENCRYPTION BY PASSWORD = 'Str0ngPWD!'
WITH SUBJECT = 'Certificate for signing stored procedures'
select * from sys.certificates
GO

create login HighPrivCertLogin from certificate HighPrivCert -- create high priv login
grant view server state to HighPrivCertLogin
GO

-- in order to grant db user rights, we must transfer to that db the same certificate
backup certificate HighPrivCert to file='C:\temp\HighPrivCert.CER'
WITH PRIVATE KEY
(    FILE = 'C:\temp\HighPrivCert.PVK',
    DECRYPTION BY PASSWORD = 'Str0ngPWD!', -- pwd to open the key
    ENCRYPTION BY PASSWORD = 'FilePWD!1' -- must protect the file
)
GO

use TestCert
create certificate HighPrivCert from file='C:\temp\HighPrivCert.CER'
WITH PRIVATE KEY
(    FILE = 'C:\temp\HighPrivCert.PVK',
    DECRYPTION BY PASSWORD = 'FilePWD!1', -- to read the file
    ENCRYPTION BY PASSWORD = 'Str0ngPWD!' -- pwd to protect the key
)
select * from sys.certificates

-- Manually delete cert files NOW!
GO

-- by signing procedure, it will run under login associated with that certificate
ADD SIGNATURE TO OBJECT::dbo.show_processes
BY CERTIFICATE HighPrivCert
WITH PASSWORD='Str0ngPWD!'
GO

execute as login='LowPrivLogin'
exec sys.sp_who2 -- shows only my session
exec dbo.show_processes -- shows all sessions! We are running under HighPrivCertLogin which have VIEW SERVER STATE permission.
revert
GO

-- Create login to which we will grant view server state. Login will be mapped to certificate.

-- Sign procedure with that certificate - meaning procedure will execute under that high-priv login.

-- certificate must be in master database in order to be mapped to login

use master -- create certificate in master database

create certificate HighPrivCert

ENCRYPTION BY PASSWORD = 'Str0ngPWD!'

WITH SUBJECT = 'Certificate for signing stored procedures'

select * from sys.certificates

create login HighPrivCertLogin from certificate HighPrivCert -- create high priv login

grant view server state to HighPrivCertLogin

-- in order to grant db user rights, we must transfer to that db the same certificate

backup certificate HighPrivCert to file='C:\temp\HighPrivCert.CER'

WITH PRIVATE KEY

( FILE = 'C:\temp\HighPrivCert.PVK',

DECRYPTION BY PASSWORD = 'Str0ngPWD!', -- pwd to open the key

ENCRYPTION BY PASSWORD = 'FilePWD!1' -- must protect the file

)

use TestCert

create certificate HighPrivCert from file='C:\temp\HighPrivCert.CER'

WITH PRIVATE KEY

( FILE = 'C:\temp\HighPrivCert.PVK',

DECRYPTION BY PASSWORD = 'FilePWD!1', -- to read the file

ENCRYPTION BY PASSWORD = 'Str0ngPWD!' -- pwd to protect the key

)

select * from sys.certificates

-- Manually delete cert files NOW!

-- by signing procedure, it will run under login associated with that certificate

ADD SIGNATURE TO OBJECT::dbo.show_processes

BY CERTIFICATE HighPrivCert

WITH PASSWORD='Str0ngPWD!'

execute as login='LowPrivLogin'

exec sys.sp_who2 -- shows only my session

exec dbo.show_processes -- shows all sessions! We are running under HighPrivCertLogin which have VIEW SERVER STATE permission.

revert

Now, let’s see the result:

As you can see in first recordset, I am still low-privileged login and user. But in second recordset you see two new rows: HighPrivCert and HighPrivCertLogin. In third recordset you can see one new permission is added: VIEW SERVER STATE. And the last recordset shows all the processes on the servers, exactly what we wanted!

You can view the list of signed code through sys.crypt_properties view:

select
  object_name = object_name(major_id),
  signed_by = cp.crypt_type_desc,
  cert_or_asymkey_name = ISNULL(c.name, ak.name),
  *
from sys.crypt_properties cp
  LEFT JOIN sys.certificates c on c.thumbprint = cp.thumbprint
  LEFT JOIN sys.asymmetric_keys ak on ak.thumbprint = cp.thumbprint

select

object_name = object_name(major_id),

signed_by = cp.crypt_type_desc,

cert_or_asymkey_name = ISNULL(c.name, ak.name),

from sys.crypt_properties cp

LEFT JOIN sys.certificates c on c.thumbprint = cp.thumbprint

LEFT JOIN sys.asymmetric_keys ak on ak.thumbprint = cp.thumbprint

This is tested on SQL2012, but should work on 2005+.

Conclusion

You can grant server-level privileges to a code (procedure, UDF, trigger) indirectly, through a code signing. We had example with sp_who2, but you can encapsulate any functionality that requires elevated privileges in a secure way, without giving that high-level privilege to a user.

7 Comments on “Procedure with Execute as login?”

Andre says:

2014-01-16 at 21:06

I liked your code and examples very much.

I took the code verbatim from your article and although it ran in SQL 2008r2, it failed to show any information outside of login LowPrivLogin for the procedure

execute as login=’LowPrivLogin’
exec dbo.show_processes

Output did show as below. Any ideas?
——————————————-
database_user login original login
LowPrivUser LowPrivLogin SW\aquitta

312 0x932E9FFE314652468C8E1EBAF7F1F028 LowPrivLogin SQL LOGIN GRANT OR DENY
2 0x02 public SERVER ROLE GRANT OR DENY

entity_name subentity_name permission_name
server CONNECT SQL
server VIEW ANY DATABASE

SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID REQUESTID
63 RUNNABLE LowPrivLogin PDX-L-AQUITTA . TestCert SELECT INTO 327 562 01/16 20:02:34 Microsoft SQL Server Management Studio – Query 63 0

- Vedran says:
  
  2014-03-05 at 09:04
  
  The results from your case show that the procedure is not executed under elevated rights. You probably did not signed the procedure, or you altered the procedure after signing it (“alter” deletes signature). Without a signature with certificate, procedure is not connected to a login, and thus does not execute under the high level priviledges granted to HighPrivCertLogin. After every ALTER of the procedure, you need to sign it again (ADD SIGNATURE TO … BY CERTIFICATE …).
  Have you tried to run the example script from the blog post, exactly as it is, without any changes from start to end – does that work for you?
  
Davorin says:

2014-03-08 at 11:20

Great article.I would like to thank you for this article coz it solved my problem for creating backup on ms sql express.While i was testing i had simular problem.When stored procedure had some error while executing,signiture was deleted so i created new procedure which task was creating signiture for second procedure before second procedure was executed.

Something like this:

If (Select Count(*)
From sys.crypt_properties cp
Left Join sys.certificates c on c.thumbprint = cp.thumbprint
Left Join sys.asymmetric_keys ak on ak.thumbprint = cp.thumbprint
Where object_name(major_id)=’sp_BackupDatabase’)=0
Begin
ADD SIGNATURE TO OBJECT::dbo.sp_BackupDatabase
By CERTIFICATE BackupCert
WITH PASSWORD=’YOUR_PASSWORD’
End

Prabu says:

2014-08-20 at 13:05

Hi your post was very useful. However I was trying to perform the below but I’m not successful. Could you please help me out.
1. I want to have my procedure in MASTER Database as system procedure so the user can call my procedure from any database.
2. We have a windows user group, user would log in to Database using their windows credentials. So how should I go about getting permission setting.
3. My basic intention is to grant all the users in the user group to be able to execute the stored procedure that in turn access the master.dbo.sysprocesses.
Thanks in Advance,
Prabu

- Vedran says:
  
  2014-08-20 at 19:38
  
  Hi Prabu,
  At which stage did you stucked at? Name the procedure to begin with “sp_” and mark it as a system procedure with “EXECUTE sp_MS_marksystemobject ‘sp_MyProc'”, so it can be called from any db without “master.” prefix. This blog post should resolve you how to execute your procedure under high-level system privileges. After that, you should grant execute permission on that proc to desired winndows group login (which have low privileges).
  
Vedran says:

2018-10-22 at 14:46

Might be helpful: Scripting certificates https://sqlity.net/en/2407/certencoded/

Vedran says:

2018-10-22 at 14:47

Might be helpful: Scripting certificates https://sqlrambling.net/2018/05/29/how-to-script-a-certificate/

Procedure with Execute as login?

7 Comments on “Procedure with Execute as login?”

Leave a Reply Cancel reply