Suppose you have users who by default have only read/write access but in certain circumstances need more permission, you don’t want to give them sysadmin just because it’s easy. Instead, you can create a stored procedure using the security context of the SQL Server service account which is member of the sysadmin server role by default.
This requires some preparation:
- The database must be configured to be trustworthy. If this is not the case run “ALTER DATABASE <dbname> SET TRUSTWORTHY ON”
- Also check the database owner. If this one is not sa, run “ALTER AUTHORIZATION ON DATABASE::<dbname> to sa;”
- The stored proc will use the [EXECUTE AS <serviceaccount>] option to have the code run using the security context of the SQL Server service account.
|CREATE PROC <procedurename>
WITH EXECUTE AS ‘<serviceaccount>’
<do some admin stuff>
- Grant execute permission to the non-admin user.
|GRANT EXECUTE ON <procedurename> TO [<databaseuser>]