Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Granting permission with the EXECUTE AS command in SQL Server


By:   |   Read Comments (2)   |   Related Tips: More > Security

Attend a SQL Server Conference for FREE >> click to learn more


Problem
One area that is often under utilized in SQL Server is issuing granular security rights at both a login and user level.  It is often easier to just give someone more rights then they usually need to perform the task at hand versus trying to figure out exactly what rights they need and then issuing just those rights.  This is often true where server level roles, such as sysadmin, are granted or database roles, such as db_owner, are granted.  By giving logins and users these rights the database user is pretty much guaranteed to have the necessary access that is needed, but granting additional rights could put your data at risk.  So what other options are there?

Solution
There are several things that can be done to determine and grant the necessary rights to the objects that are needed.  These features have been available in SQL Server for a long time, but in SQL Server 2005 a new feature has been added "EXECUTE AS" which allows you to impersonate another user in order to validate the necessary permissions that are required to execute the code without having to grant all of the necessary rights to all the underlying objects and commands.

The EXECUTE AS clause can be added to stored procedures, functions, DML triggers, DDL triggers, queues as well as a stand alone clause to change the users context.  This clause is simply added to the code as follows:

CREATE PROCEDURE dbo.TestProcedure
WITH EXECUTE AS OWNER

There are basically five types of impersonation that can be used:

  • SELF - the specified user is the person creating or altering the module
  • CALLER - this will take on the permissions of the current user
  • OWNER - this will take on the permissions of the owner of the module being called
  • 'user_name' - a specific user
  • 'login_name' - a specific login

The way SQL Server already works is that in most cases you only need to grant execute rights to the stored procedure and rights are granted to all objects that are referenced within the stored procedure, so you do not need to give implicit rights to either update data or call additional stored procedures.  This is handled via ownership chaining.  This holds true for statements such as these, but when you deal with dynamic SQL or if you need to perform other tasks such as creating tables, this is where the EXECUTE AS clause comes in handy.

Here is sample stored procedure code that creates a table, selects the top 5 rows from another table and then inserts the rows into the new table that was created.  The user "test" has no rights in the database except execute rights on the calling stored procedure.

CREATE PROCEDURE dbo.usp_Demo2
AS
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].table_2') AND type in (N'U'))
CREATE TABLE table_2 (id int, data nchar(10))

INSERT INTO table_2

SELECT top 5 * from dbo.table_1;

GO

GRANT EXEC ON dbo.usp_Demo2 TO test;
GO

EXEC dbo.usp_Demo2;

When this is created and run you get this error message:

Our second version of this stored procedure uses the EXECUTE AS clause.

CREATE PROCEDURE dbo.usp_Demo2
WITH EXECUTE AS OWNER
AS
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].table_2') AND type in (N'U'))
CREATE TABLE table_2 (id int, data nchar(10))

INSERT INTO table_2

SELECT top 5 * from dbo.table_1;

GO

GRANT EXEC ON dbo.usp_Demo1 TO test;
GO

EXEC dbo.usp_Demo1

When this is created and ran the code is successful.

The first set of code fails, because user "test" does not have permission to create new tables. We could grant create table rights to this user, but this then opens up another issue where this user has rights to create any table they want. With the EXECUTE AS clause the stored procedure is run under the context of the object owner and therefore when this code executes the creation of table dbo.table_2 is created and we are able to insert the rows successfully.  In addition, since this table has been created under the OWNER context the user "test" has no rights to modify the table or read or modify any of the data in this table.  Therefore it only takes on the rights needed to complete this specific task or function.

This is a very simple example of this new feature, but this should give you an idea on how the EXECUTE AS clause can make handling permissions a lot easier for certain things you may face.

Next Steps



Last Update:


signup button

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, March 05, 2014 - 4:30:12 PM - Joanne Back To Top

Hi Greg - I am trying to resolve an issue where I'm getting 'Could not obtain information about Windows NT group/user' when I try to use 'exec as' with a windows domain user or login  either within a stored procedure or outside of it.  We are using sql server 2005 currently.  It works when I use a sql server login.  I'm thinking I might need sys admin help on this one?  Or should I consider some other cause? Thanks for you help!


Wednesday, June 13, 2012 - 2:26:33 AM - Vicky Back To Top

Hi Greg,  Thanks for your post and for the detailed explanation. My questions is:  Can we pass a parameter to stored procedure so that we can replace username with the passed one?  e.g: .....Execute AS @username? 

 

 

Thanks

Vicky

 


Learn more about SQL Server tools