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.