SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Simple Database User-Access Security Validation & Granting Elevated Access Temporarily Using the Execute As Statement


If you ever want to validate what level of access a user has within a database OR wish to temporarily give elevated access to a database user’s object code (e.g. within a stored procedure) and not provide them directly with the privileges, you can profit from the Execute As Transact-SQL statement:

Execute as user=’domain\login’
-- run the code that needs the elevated access

If you want to take out these privileges at any point within the code, you simply run a REVERT statement, or execute another Execute As statement.  Once the session is over, the effective permissions are gone for the user within the scope of the database in question.  

Microsoft has updated its primary
SQL 2008 security page recently, and it includes this recommendation in a more technical description:

Enhance security features with execution context

Mark modules with an execution context so that statements within the module execute as a particular user instead of the calling user

Grant the calling user permission to execute the module, but use the permissions of the execution context for statements within the module

If you wish to a Deep Dive into Compliance-Oriented Database Security, listen to my MVP friend Paul DeBetta and Robert Woodard,
in their TechNet Webcast. They explain encryption very well for those who are weak on this aspect of database security, such as myself J
For a full in the trenches style Database Administrator Security Guide, please read
my previous post on Best Practices for the Vigilant DBA, or on TechNet itself for the abridged version.



No comments.

Leave a Comment

Please register or log in to leave a comment.