By:   |   Comments (6)   |   Related: > Security


One area that is often under utilized in SQL Server, is issuing granular security rights at both the login and user level.  It is often easier to just give someone more rights then they 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?


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 SQL Server offers 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

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 a 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.

   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; 

GRANT EXEC ON dbo.usp_Demo2 TO test;

EXEC dbo.usp_Demo2;

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

error message

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

   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; 

GRANT EXEC ON dbo.usp_Demo1 TO test;

EXEC dbo.usp_Demo1

When this is created and ran, the execution is successful.

rows returned


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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, March 29, 2022 - 8:21:19 PM - Ramana Mysore Back To Top (89953)
Thanks Greg,
This was helpful in resolving the issue for me today.
Though the user executing the SP had execute permission on the SP, i was using dynamics SQL to select rows from a table and it was failing with error select permission not there on the table.
With this fix it worked!

Saturday, February 6, 2021 - 10:57:11 PM - Katherine Back To Top (88193)
This is helpful for me, thank you!

Wednesday, November 4, 2020 - 9:55:25 AM - Greg Robidoux Back To Top (87756)
Felix, look at this article - https://www.mssqltips.com/sqlservertip/6608/install-sql-server-2019-standard-edition/


Wednesday, November 4, 2020 - 8:48:56 AM - Felix oseh Back To Top (87755)
Please I need a procedure on how to install SQL server on windows 10

Wednesday, March 5, 2014 - 4:30:12 PM - Joanne Back To Top (29650)

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 (17956)

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? 






get free sql tips
agree to terms