Permission Context

Earlier in this chapter, a classic security problem was brought up. If it's a best practice to always use stored procedures, how do I handle dynamic SQL inside of a stored procedure? To illustrate this problem, let's use the same database and table from the previous section in this chapter. In that example, you had a user named SchemaExampleLogin. This user did not have any rights to the dbo schema other than to add new tables into it. He could not select from any table in the dbo schema, however. This is shown if you login as the SchemaExampleLogin and use a password of schemapass08. Once logged in, attempt to query the dbo.TestTableProblem table:

 SELECT * FROM dbo.TestTableProblem 

You will then receive the following error:

 Msg 229, Level 14, State 5, Line 1 SELECT permission denied on object 'TestTableProblem', database 'SchemaExample', schema 'dbo'. 

The best practice mentioned earlier is not to grant the SchemaExampleLogin user access to the TestTableProblem table but instead create a stored procedure. This allows you to tightly control access to your tables through canned queries that you've already approved. More benefits are listed in Chapters 6 and 7. So, to quickly fix the problem, you could create a stored procedure as shown in the following example:

 CREATE PROC DynamicSQLExample AS SELECT * FROM dbo.TestTableProblem GO 

The stored procedure would then be called and would return the expected results:

 EXEC DynamicSQLExample 

Note

Throughout the example in this section, you will need to flip back and forth from the lower-privileged account SchemaExampleLogin to a database owner or sysadmin. Run the code that will create the stored procedures with the higher privileged account and the execution of the stored procedures with the SchemaExampleLogin user.

The problem crops up when you have a stored procedure with dynamic SQL. Dynamic SQL allows a T-SQL programmer to create a looser stored procedure that can order the data dynamically or only select certain columns. They should always be used sparingly, since the Query Optimizer won't be able to give you the same optimized query plan as with a canned stored procedure. Typically, you will see these types of queries with a search screen in an application where a user may type any number of fields to search. You can alter the previous stored procedure as shown below to make it dynamic.

 ALTER PROC DynamicSQLExample @OrderBy Varchar(20) AS DECLARE @strSQL varchar(255) SET @strSQL = 'SELECT * FROM dbo.TestTableProblem ' SET @strSQL = @strSQL + 'ORDER BY ' + @OrderBy EXEC (@strSQL) GO 

If you try to execute the stored procedure like this:

 DynamicSQLExample 'column1' 

This will give the same error that you saw before stating that you must have SELECT permission to the underlying table before you can run the stored procedure.

 Msg 229, Level 14, State 5, Line 1 SELECT permission denied on object 'TestTableProblem', database 'SchemaExample', schema 'dbo'. 

The old way to solve this problem in SQL Server 2000 was to go ahead and grant the SchemaExampleLogin user more rights than you wanted and hope they exercised caution when selecting from the table. In SQL Server 2005, you can use context switching to impersonate another user for a short duration.

EXECUTE AS Command

Now that you know the previous problem, context switching will hopefully give you the best relief. Context switching will temporarily give the user executing the stored procedure higher rights but only in the scope of the stored procedure's execution. As soon as the stored procedure has completed executing, the permissions are reverted. Additionally, you can use context switching to troubleshoot permission problems. You can impersonate another login to see what problems they may be seeing and try to reproduce the difficulties.

Context switching is done through the EXECUTE AS command. It's similar to the old SETUSER command, but SETUSER required that you be a sysadmin or db_owner to run. At its simplest form, the syntax would either state EXECUTE AS USER or EXECUTE AS LOGIN. As you can guess, this will either allow you to impersonate a user or a login. In the previous example, you could create an additional user with very minimal rights to given tables that are needed. Then you could impersonate that user in the stored procedure and revert to the old permissions afterward. Some people like to create many new users for each type of access and then do impersonation based on need.

Before you can do this type of context switching, though, the user that you wish to impersonate must grant you access to do the impersonation. Otherwise, you would receive the following error anytime someone tried to run a query with context switching.

 Msg 15517, Level 16, State 1, Procedure DynamicSQLExample, Line 5 Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. 

To grant someone rights to impersonate your user, you can use the GRANT statement or go to the Securables page in the User Properties dialog box. For example, to grant the SchemaExampleLogin user rights to impersonate the dbo account, use the following syntax:

 GRANT IMPERSONATE ON USER:: dbo TO SchemaExampleLogin; 

This syntax would be a worst practice, since you're granting the user rights to impersonate a very sensitive account. The dbo user would have rights to do anything you would like inside the database. You should instead grant the user rights to impersonate a lower-privileged account. Since we have no other users for this example, the previous code will work fine, temporarily.

The next step is to alter the previous stored procedure to enable context switching. You can see the EXECUTE AS statement in the following code, right before the SELECT statement. There is also quite a bit of debugging code in the stored procedure to show what context you're currently using. The SUSER_NAME function shows what login you're currently using. The USER_NAME function shows the user name that you're currently using, and the ORIGINAL_LOGIN function shows what your original login was before the context switch. ORIGINAL_LOGIN is a key function to use to help with auditing.

 ALTER PROC DynamicSQLExample @OrderBy Varchar(20) AS EXECUTE AS USER = 'dbo' DECLARE @strSQL varchar(255) SET @strSQL = 'SELECT * FROM dbo.TestTableProblem ' SET @strSQL = @strSQL + 'ORDER BY ' + @OrderBy EXEC (@strSQL) SELECT SUSER_NAME() as LoginNm, USER_NAME() as UserNm, ORIGINAL_LOGIN() as OriginalLoginNm; GO 

With the stored procedure now modified, go ahead and execute it again while logged in as SchemaExampleLogin:

 DynamicSQLExample 'column1' 

You should now see results from the query. You should also see the login and user names that you're impersonating. After the stored procedure runs, go ahead and rerun the following chunk of code to see what user you are currently using:

 SELECT SUSER_NAME() as LoginNm,    USER_NAME() as UserNm,    ORIGINAL_LOGIN() as OriginalLoginNm; 

Now your user has reverted to the SchemaExampleLogin. This shows that when using context switching in a stored procedure, it reverts as soon as the stored procedure completes running.

If you were to manually connect with the SchemaExampleLogin and run the following query, you could see another point.

 EXECUTE AS USER = 'dbo' GO USE AdventureWorks GO 

When you're switching context to another user (not login), you will receive an error when trying to leave the database.

 Msg 916, Level 14, State 1, Line 1 The server principal "BKNIGHT\Owner" is not able to access the database " AdventureWorks" under the current security context. 

The solution, if you must do this, is to switch context to a login, not a user, as shown in the following code. You should not switch to a login context if only database-level items are needed. Otherwise, you may be giving the user more rights than needed, and you may be opening yourself up to hackers. Use login context switching very sparingly.

 EXECUTE AS login = 'Bknight' 

The keyword REVERT will revert the user's permissions to the previous context. If you have switched context multiple times, it will only revert you a single switch, and you may have to issue REVERT several times before going back to your original user. If you had switched context to a login and then began to use a different database, you would receive the following message when you tried to issue a REVERT command. This will force you to then go back to the original database to issue the REVERT.

 Msg 15199, Level 16, State 1, Line 1 The current security context cannot be reverted. Please switch to the original database where 'Execute As' was called and try it again. 

Troubleshooting Permission

As you have seen throughout this chapter, if you don't really configure the permissions appropriately, it can be very tricky to troubleshoot. There is a new function that will help you figure out what permissions a given user has available before you have to dig through many screens in Management Studio. The function is fn_my_permissions, and it shows what permissions the caller has by default. For example, if you wish to see what access you have to the TestSchema schema, you can use the following syntax:

 SELECT * FROM fn_my_permissions('TestSchema', 'SCHEMA') 

The second input parameter is the type of object, and the first parameter is the name of the specific object. To view what permissions you have for the dbo.TestTableProblem table, you can run the following syntax. If nothing is returned, either you have typed the wrong input parameter or you have no permissions. The input parameter OBJECT applies for all the basic database objects (tables, stored procedures, views, and so on).

 SELECT * FROM fn_my_permissions('dbo.TestTableProblem', 'OBJECT') 

You can also find out what permissions a user has to the server by passing in the parameter of SERVER with an input parameter of NULL for the first parameter. In addition, you can use context switching to see what permissions someone else has when trying to debug a problem, as shown here:

 EXECUTE AS Login = 'Bknight' SELECT * FROM fn_my_permissions(NULL, 'SERVER') REVERT 

If you're trying to figure out if you can impersonate someone else, you can use the input parameter of USER and pass in the user name. The same applies for the parameter of LOGIN.

 SELECT * FROM fn_my_permissions('SchemaExamplelogin', 'USER');