SQL Server Impersonation thru stored procedures

· Uncategorized
Authors

Suppose you have users who by default have only read/write access but in certain circumstances need more permission, you don’t want to give them sysadmin just because it’s easy. Instead, you can create a stored procedure using the security context of the SQL Server service account which is member of the sysadmin server role by default.

This requires some preparation:

  • The database must be configured to be trustworthy.  If this is not the case run “ALTER DATABASE <dbname> SET TRUSTWORTHY ON”
  • Also check the database owner. If this one is not sa, run “ALTER AUTHORIZATION ON DATABASE::<dbname> to sa;”
  • The stored proc will use the [EXECUTE AS <serviceaccount>] option to have the code run using the security context of the SQL Server service account.
CREATE PROC <procedurename>
WITH EXECUTE AS ‘<serviceaccount>’
as
<do some admin stuff>
GO
  • Grant execute permission to the non-admin user.
GRANT EXECUTE ON <procedurename> TO [<databaseuser>]
GO

 

 

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

%d bloggers like this: