By: Greg Robidoux | Updated: 2020-08-14 | Comments (6) | Related: > Security
Problem
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?
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 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 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 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.
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 execution is successful.
Summary
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
- Check out EXECUTE AS in books online
- Take a look at existing instances where you have had to grant additional rights to users where this feature could replace the extra rights that are not needed all of the time.
- Review your overall database and server rights. Check out these tips on how to do this:
About the author
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
Article Last Updated: 2020-08-14
