Site icon Sql Quantum Leap

PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining

Solomon Rutzky

(last updated: 2021-11-12 @ 11:45 ET / 2021-11-12 @ 16:45 UTC )

CONTENTS

  1. Common “Problem” Scenarios
     
  2. Security Basics
    1. Logins
    2. Users
    3. PRINCIPAL_IDs
    4. Security Identifiers (SIDs)
    5. Permissions
    6. Ownership Chaining
       
  3. Impersonation
    1. EXECUTE AS Clause
    2. EXECUTE AS Statement
    3. AS Clause of EXECUTE ('string')
    4. Reverting Within a Sub- (i.e. Nested) Process
       
  4. Cross-Database Ownership Chaining
     
  5. TRUSTWORTHY ON
     
  6. REASONS TO AVOID THESE METHODS
     
  7. Module Signing
    1. Signature
    2. Counter Signature
       
  8. BENEFITS OF MODULE SIGNING
     
  9. Asymmetric Keys and Certificates
    1. Common Aspects
    2. Asymmetric Keys
    3. Certificates
    4. How Asymmetric Keys and Certificates are Used
       
  10. The Only Valid Uses of Impersonation
    and TRUSTWORTHY
    1. Impersonation
    2. TRUSTWORTHY
       
  11. Examples
     
  12. Conclusion

Despite features added in SQL Server 2005 (yes, 2005!) that allow for very flexible, granular, and robust security, it is still quite common for people to be using the riskier mechanisms of temporarily granting additional privileges.

What follows is an overview of a presentation that I have given several times, and will continue presenting, on the topic of Module Signing, and why it is far superior to using any combination of Cross-Database Ownership Chaining, Impersonation, and TRUSTWORTHY:

Module Signing or: How I Learned to Stop Using TRUSTWORTHY / EXECUTE AS and Love Certificates


 
 

Common “Problem” Scenarios

Proper security starts with giving users the lowest set of permissions necessary. Quite often we run into a situation where a user is not able to execute something with their minimal permissions. Those situations are most often the following:


 
 

Security Basics


 

Logins


 

Users


 

PRINCIPAL_IDs

principal_ids are INT values that only exist within SQL Server. These are the PK fields in the sys.server_principals and sys.database_principals system catalog views. You will find these values in other system catalog views to assign ownership, permissions, etc.

These values only exist in their specific context: server principal_ids are only used for instance/server -level ownership, permissions, etc; database principal_ids are only used for database-level ownership, permissions, etc, and only in that one particular database. principal_id values in one context do not equate to values in any other context; and if they do happen to be the same, that is only by coincidence. Meaning, there is no way to map a Login to any of its associated Users by using its principal_id.


 

Security Identifiers (SIDs)

SIDs are used at the OS / Active Directory level to uniquely identify resources: logins, groups, computers, etc. SID values are based on a meaningful structure that includes the type of resource that they are and other information about them. They are used in the Registry, in ACLs, etc, and can be represented in either a binary form or a string form (SDDL).

SIDs created outside of SQL Server are brought into SQL Server when creating Logins based on Windows Logins or Windows Groups. SIDs created within SQL Server, especially those used for Server Roles and SQL Server Logins (and their optional, associated Users), have no meaning outside of SQL Server.

SIDs for SQL Server logins:
1. are essentially GUIDs (i.e. UNIQUEIDENTIFIER)
1. can be provided via the SID = argument of the CREATE LOGIN statement

Within SQL Server, SIDs are used to:

  1. link the Login in SQL Server to the Windows Login or Windows Group that the Login is based on (for Windows Logins, not SQL Server Logins)
  2. link Logins (all types; instance-level) to their associated Users (if any; database-level)

The undocumented (hence, unsupported) built-in function SID_BINARY() can be used to translate an SID from string form into its binary form, but only for “properly” structured SIDs (i.e. does not work with SIDs used for SQL Server Logins or Server Roles):

SELECT SID_BINARY(N'S-1-5-21-328604631-1550768051-1039272773-1002');
-- 0x010500000000000515000000D71B9613B3D76E5C450BF23DEA030000

SIDs are stored in SQL Server as VARBINARY(85).


 

Permissions

DENY overrides any GRANT. The only exception is when a DENY is on an object referenced in an object that a principal has been granted access to, and the owner of both objects is the same. In this case, the permissions are only checked for the initial / outer object being accessed, and then Ownership Chaining (see immediately below) takes over and assumes permissions have been granted on all referenced objects having the same owner. Technically speaking, this isn’t a DENY not overriding a GRANT, it’s merely that the DENY is never seen in the first place.

Download the official poster sized chart of all Database Engine Permissions for Microsoft SQL Server 2017 and Azure SQL Database (1.44 MB PDF file).


 

Ownership Chaining

Ownership chaining is the basic security mechanism that allows Stored Procedures, Views, etc to be an API to the data model (i.e. the Tables) so that permissions do not need to be granted to any Tables. Anyone who has permission to access a particular object will be able to access any objects referenced in that object as long as the referenced objects are owned by the User that owns the initial object. Object ownership is determined by:

Ownership chaining does not need to be enabled, and cannot be disabled; it is simply how SQL Server works. Assumed permissions apply to DML, SELECT, and EXEC[UTE] statements, but not to DDL statements or statements like SET IDENTITY INSERT ON, TRUNCATE TABLE, etc.

Fewer permissions checks on objects should improve performance (even if only slightly). However, not checking permissions on objects within the ownership chain does allow for an explicit DENY to be ignored (just something to be aware of).

By default, Ownership chaining does not work across databases (see “Cross-Database Ownership Chaining”).

Using Dynamic SQL breaks ownership chaining, and permissions will be reevaluated as the Dynamic SQL is being compiled. The permissions error is often solved by using EXECUTE AS (i.e. Impersonation), but that is a security risk.


 
 

Impersonation

Impersonation can be thought of as being “Instead Of” permissions. This is due to the current set of permissions being switched out (completely) for the set of permissions associated with the Login or User being impersonated. The session is operating with a new set of permissions instead of the old ones. Also, the Security ID (SID) of the executing User changes to be the SID of the User being impersonated. These change are reflected in the values returned by the Instance-level SUSER_NAME() / SYSTEM_USER and Database-level USER_NAME() / SESSION_USER functions.

I consider this means of managing permissions to be “account-based” security since it is a real Login or User that is being impersonated in order to assume their permissions. The user starts out with their permissions but then becomes an entirely different account with an entirely different set of permissions.

Originally the User (not Login) context could be impersonated using the SETUSER statement. However, this statement was deprecated as of at least SQL Server 7.0 (I don’t have earlier documentation to check), with no replacement provided until SQL Server 2005. So, do not ever use SETUSER (when using SQL Server 2005 or newer)!

Becoming a different account is accomplished using EXECUTE AS, added in SQL Server 2005. There are two or three ways to use EXECUTE AS (three ways to specify, but two have identical behavior):


 

Clause

The EXECUTE AS clause is part of the CREATE statement for: Stored Procedures, DML Triggers, and most types of Functions. The EXECUTE AS clause is not available for Views or T-SQL Inline Table-Valued Functions (iTVFs).

The principal_id to impersonate using this clause can only be a Database-level User.

When using this clause, whoever is able to execute this module does not need to have explicit IMPERSONATE permission granted on the to-be-impersonated User. This allows you to specify EXECUTE AS OWNER or EXECUTE AS N'dbo' without requiring that you allow the executing User to impersonate this principal whenever they want (which would be the case if you granted IMPERSONATE permission to the User).

The impersonated context is in effect until the module ends. Executing REVERT within a module creating with this clause has no effect.

By default, the process is confined / quarantined to the local / current Database while the impersonated context is in effect. The only way to break out of the quarantine to reach up to the Instance-level or across to another Database, as the same User, is to set the Database property of TRUSTWORTHY to ON. Otherwise, with TRUSTWORTHY set to OFF, you are only permitted to access Databases in which the guest User has been enabled (by granting it the CONNECT permission). The system Databases of master, msdb, and tempdb all have the guest User enabled, but model does not. When using another Database as the guest User you can only access what has been granted to the public Role.

When working with Instance-level Triggers (i.e. DDL and Logon Triggers), the principal_id to impersonate can only be a Instance-level Login.


 

Statement

The EXECUTE AS statement can be executed in any T-SQL batch or within Stored Procedure and Triggers. It cannot be executed within a View or any type of Function.

Unlike the EXECUTE AS clause, the statement can be used to impersonate Instance-level Logins as well as Database-level Users. Another difference is that when using the statement, explicit IMPERSONATE permission is required on the principal being impersonated.

When using the EXECUTE AS LOGIN statement, you can USE any Database in which a User, having the same SID as the impersonated Login, has been created. In this case, you would be the same User you were in the original Database, and not the guest User, even when TRUSTWORTHY is set to OFF in the original Database.

When using the EXECUTE AS USER statement, the process is confined / quarantined to the local / current Database by default. Just like with the EXECUTE AS clause, the only way to break out of the quarantine to reach up to the Instance-level or across to another Database, as the same User, is to set the Database property of TRUSTWORTHY to ON. Otherwise, with TRUSTWORTHY set to OFF, you are only permitted to access Databases in which the guest User has been enabled (by granting it the CONNECT permission). The system Databases of master, msdb, and tempdb all have the guest User enabled, but model does not. When using another Database as the guest User you can only access what has been granted to the public Role.

The impersonated context is in effect until either the sub-process or Session ends (depending on when EXECUTE AS was executed), or REVERT is executed (at the same nesting level). The impersonated context cannot be reverted in:


 

AS { LOGIN | USER } Clause of the EXECUTE ('string') Command

When doing context switching via the EXECUTE command (i.e. EXECUTE (N'string') AS { LOGIN | USER } = N' name ' ), the requirements and behavior are the same as with the EXECUTE AS statement (specifically within the context of a sub-process). This is why there are really only two ways to do Impersonation.


 

Reverting Within a Sub- (i.e. Nested) Process

While the REVERT statement does not work within a sub-process (a.k.a. a nested process), you can use the EXECUTE AS CALLER; statement to revert back to the caller of that sub-process. And, to get back to the original security context of the sub-process, that is the only time that REVERT; does work within a sub-process.

For example:

SELECT 1, SESSION_USER AS [User], SYSTEM_USER AS [Login]; -- Original
EXEC(N'
  SELECT 2, SESSION_USER AS [User], SYSTEM_USER AS [Login]; -- Impersonated
  REVERT;
  SELECT 3, SESSION_USER AS [User], SYSTEM_USER AS [Login]; -- Impersonated
  EXECUTE AS CALLER;
  SELECT 4, SESSION_USER AS [User], SYSTEM_USER AS [Login]; -- Original
  REVERT;
  SELECT 5, SESSION_USER AS [User], SYSTEM_USER AS [Login]; -- Impersonated
') AS LOGIN = N'principal_being_impersonated';
SELECT 6, SESSION_USER AS [User], SYSTEM_USER AS [Login]; -- Original


 
 

Cross-Database Ownership Chaining

By default, ownership chaining only works within a Database and does not extend out to other Databases (or even up to the Instance-level). The simplest way around this restriction is to enable Cross-Database Ownership Chaining, added in SQL Server 2000. There are two ways to accomplish this:

  1. Enable for all Databases at the Instance-level: 
      EXEC sp_configure 'cross db ownership chaining', 1;
  RECONFIGURE;

    When this setting is enabled, all Databases are enabled and cannot be disabled individually. When this setting is disabled, Databases can be enabled individually, at the Database-level.

  2. Enable for individual Databases at the Database-level:

      ALTER DATABASE { [database_name] | CURRENT } SET DB_CHAINING ON;

    When the Instance-level setting is enabled, this setting has no effect. When the Instance-level setting is disabled, this setting is used to enable Databases individually.

Cross-Database ownership chaining requires that the same SID for the owner of the objects exist in both Databases. This should probably be expected, because otherwise the objects cannot truly have the same owner. What might not be expected is that the User accessing the object (i.e. the “caller”) also needs to exist in the other Database(s). They don’t need to have any permissions assigned in the other Database(s), they just need to exist with the same SID. However, they will have access to everything that is available to the public Role.

When using Dynamic SQL to access the other Database(s), Cross-Database ownership chaining doesn’t work, same as with regular ownership chaining. In this case, a combination of Impersonation (i.e. EXECUTE AS) and TRUSTWORTHY ON will “fix” the error, but then you are no longer using Cross-Database Ownership Chaining (it can even be disabled), and are instead using something that is a much larger security risk.

To be clear, Cross-Database Ownership Chaining, just like ownership chaining, cannot be used to elevate permissions. It can only be used to assume basic permissions to allow for DML, SELECT, and EXEC[UTE] statements.


 
 

TRUSTWORTHY ON

Added in SQL Server 2005, this is a Database property that tells the Instance to trust User SIDs from this Database. Hence, a SID at the Instance-level, or in another Database, that happens to be the same as a SID in a TRUSTWORTHY Database will be “trusted” to be the same account (meaning that the reason that the SIDs match is due to the accounts actually being the same, and not due to coincidence at best, or a hacking attempt at worst). With this setting enabled, the process is not quarantined to the current Database. The process is allowed to interact with the Instance and/or other Databases, just so long as there is a matching SID in that location. When using Impersonation, the SID of the process is the SID of the impersonated User / Login, not the caller’s SID.

This setting is OFF by default.

Enable or disable using:

ALTER DATABASE { Name | CURRENT } SET TRUSTWORTHY { ON | OFF } ;

This setting is often used to:

While setting TRUSTWORTHY ON is quicker and easier than setting up proper security, it’s a huge security risk, and is never required (not even for loading unsupported .NET Framework libraries).


 
 

REASONS TO AVOID THESE METHODS

Admittedly, using Cross-Database Ownership Chaining and/or Impersonation and/or TRUSTWORTHY are quicker and easier to implement than Module Signing. However, the relative simplicity in understanding and implementing these options comes at a cost: the security of your system, and to a lesser degree, performance.

The common theme across all three areas is no control, within a Database, over who or what can make use of the feature / option, or when it can be used.


 
 

Module Signing

Module Signing can be thought of as being “In Addition To” permissions. This is due to the current set of permissions remaining (unchanged) while one or more new permissions are added to the current security context. The session is operating with the original set of permissions combined with a new set of permissions. Also, the Security ID (SID) of the executing User does not change.

I consider this means of managing permissions to be “code-based” security since permissions are effectively being granted to modules instead of accounts. Even though a Login and/or User, created from the Asymmetric Key or Certificate, are required to contain the additional permissions, the Key / Certificate -based principals are merely containers for the new permissions (i.e. proxies) and can neither connect to SQL Server nor be impersonated. The executing User does not change (SID and permissions remain the same). But, once code that has been signed begins executing, permissions granted to a principal created from the same Certificate or Asymmetric Key that was used to sign the code are added to the security context.

The following types of modules can be signed:

The following types of modules cannot be signed:

Undocumented objects that can be signed:

The actual signatures are stored in sys.crypt_properties.

Signing a module is accomplished using ADD SIGNATURE. There are two ways to use ADD SIGNATURE:


 

Signature

Adding a signature is simply applying a Certificate or Asymmetric Key to a module. Using a regular signature (i.e. not including the word COUNTER in the ADD SIGNATURE statement) allows any module, signed with the specified Certificate or Asymmetric Key, to inherit any permissions granted to any principal created from that same Certificate or Asymmetric Key.

The additional permissions apply to only the signed module(s). Any modules referenced by the signed module do not get the additional permissions. Remember, Module Signing is a very precise mechanism, and permissions are only applied to modules that have been explicitly signed. If modules referenced by the signed module also need additional permissions, then they will also need to be signed.


 

Counter-Signature

When modules referenced by the signed module also need additional permissions, then you have a choice for how to proceed. You can sign the referenced modules, as noted above, or you can counter-sign it. Counter-signing is very similar to regular signing except that it can only be used to keep the additional permissions (associated with the Certificate or Asymmetric Key), that were provided to a calling module, flowing down to a referenced module.

Meaning, if Module A is signed and calls Module B, by default Module B will not have any of the extra permissions that were given to Module A via the signature. However, if Module B were to be counter-signed using the same Certificate or Asymmetric Key that was used to sign Module A, then Module B would also have those extra permissions. Now, the difference between signing Module B and counter-signing Module B is that, when counter-signing Module B, those extra permissions will only be there if Module B is called by Module A; if Module B is executed directly, or from some other module that isn’t signed by the same Certificate or Asymmetric Key, then the extra permissions will not be added to the process.

While this is a rather interesting feature / ability, it seems to have very limited usefulness. The only benefit would be for scenarios in which:

Some possible scenarios that would benefit from counter-signing are:

  1. Stored Procedure A contains Dynamic SQL (which breaks ownership chaining). User Bob is allowed to execute Stored Proc A which will work correctly when referencing objects that Bob owns or has been given explicit permissions on. There are other objects that Bob neither owns nor has permissions on, and you want to restrict which ones can be acted upon by Stored Procedure A (when executed by Bob), or you need to enforce a particular workflow or set of business rules for those other objects.

    If you counter-sign Stored Procedure A, then you can sign Stored Procedure B which executes Stored Procedure A in predefined ways. Giving Bob EXECUTE permission on both Stored Procedures allows Bob to use Stored Procedure A in an open-ended manner, but only on objects that Bob could manipulate anyway. But, Bob would only be able to use Stored Procedure A on restricted objects by executing Stored Procedure B which has additional steps to control the interaction with those restricted objects. And, if Bob attempts to alter Stored Procedure B so that it will do something else, the signature will be dropped and it will no longer be able to access the restricted objects.

  2. Multi-statement Table-Valued Function (TVF) C selects from a Dynamic Management View (DMV) that only returns rows related to the current Login unless the current Login is a member of one of the Instance-level Roles that allows for getting all rows (e.g. sys.dm_exec_sessions, sys.dm_exec_requests, etc). It also joins a few other tables and has some derived fields (i.e. column based on an expression) such that it is not a simple SELECT * FROM dmv and makes sense to have encapsulated as a module. Sally should be able to SELECT from TVF C as it will only return rows for Sally. Sally also needs to execute a maintenance Stored Procedure D that uses the results of TVF C, but this maintenance operation requires seeing all rows that could be returned from that DMV, not just for Sally. You do not want to duplicate the complex query that is in the TVF as that is the reason you created the TVF: code re-use. The DMV requires Instance-level permission in order to return all rows (e.g. VIEW SERVER STATE) and you correctly do not want to enable TRUSTWORTHY in order to get that Instance-level permission (since adding WITH EXECUTE AS N'dbo' to the CREATE PROCEDURE statement is confined to the Database-level, unless evil TRUSTWORTHY is enabled).

    If you sign Stored Procedure D, counter-sign TVF C, and GRANT VIEW SERVER STATE to a Login created from the Certificate used to sign and counter-sign C and D, then TVF C will have VIEW SERVER STATE only when referenced within Stored Procedure D. When Sally selects from TVF C outside of Stored Procedure D, that Instance-level permission will not be added to the process because the TVF was merely counter-signed.

Please ignore the “Countersignatures” example scenario in the Microsoft documentation for ADD SIGNATURE as it is an invalid case. It would be simple enough to just:

A counter-signature doesn’t provide any benefit for that example scenario. If you haven’t yet seen that example, don’t bother 😉.


 
 

BENEFITS OF MODULE SIGNING

 

Asymmetric Keys and Certificates


 

Common Aspects


 

Asymmetric Keys


 

Certificates


 

How Asymmetric Keys and Certificates are Used

Encryption

Signing

Module Signing in SQL SERVER

Execute signed module:

  1. Look in sys.crypt_properties for thumbprint (hash of Public Key) and crypt_property (signature of the signed module) based on major_id (in this case, matches object_id of current module)
  2. Look in sys.certificates for public key and sid based on thumbprint (from step 1)
  3. Use crypt_property (from step 1) and public key (from step 2) along with definition of module (source code of current module) to determine if current definition is identical to what it was when the module was signed:
    1. If “not identical” then quit (do not apply any additional permissions, which might result in an error)
    2. If “identical”, continue
  4. Use sid (from step 2) to check for a matching Login and/or User:
    1. If a Login is found, add its instance-level permissions to the current security context
    2. If a User is found, add its database-level permissions to the current security context

 

The Only Valid Uses of Impersonation and TRUSTWORTHY ON


 

Impersonation

Starting with SQL Server 2005, the only reasons to use Impersonation are:

  1. Creating a sandbox for Dynamic SQL to execute in. This allows for restricting an application Login / User to an even narrower set of permissions than they hopefully already have (assuming that normally they can only execute a specific set of stored procedures). You can use the WITH COOKIE INTO @variable option to prevent someone from being able to issue REVERT within the Dynamic SQL while still allowing you to REVERT WITH COOKIE = @variable; after the Dynamic SQL executes. For example: 
        DECLARE @SQL NVARCHAR(MAX),
            @CIsForCookie VARBINARY(8000);

    SET @SQL = N'some T-SQL here, yo!';

    EXECUTE AS USER = 'SandBoxedUser' WITH COOKIE INTO @CIsForCookie;

    EXEC(@SQL);

    REVERT WITH COOKIE = @CIsForCookie;

     

  2. For testing, to temporarily become a lower-privileged User to make sure that the security setup actually works as intended. In these cases, it is usually the EXECUTE AS LOGIN statement that is used to temporarily become the testing account.
     
  3. To allow Module Signing to work outside of the current / active Database (i.e. reach up to the Instance-level and/or over to other Databases) when the process is initiated as a Database-only context. In these cases, it is the EXECUTE AS clause that is used. The requirements to get this working are:
    1. It doesn’t matter who the to-be-impersonated User is, it is just required to have this clause in order to break out of the very strict quarantine imposed by this context. It can be a User created without a Login.
    2. Each module in the chain, starting with the module created using the WITH EXECUTE AS clause, needs to be signed with the Certificate.
    3. IF Instance-level access is needed:
      1. Copy the Certificate (no need to copy Private Key) to [master]
      2. Create a Login from that Certificate
      3. Grant AUTHENTICATE SERVER permission to the Cert-based Login
      4. Grant any additional permissions needed and/or add Login to any potential Instance-level Roles
    4. IF access is needed in another Database:
      1. Copy the Certificate (no need to copy Private Key) to the other Database(s)
      2. Create a User from that Certificate in the other Database(s)
      3. Grant AUTHENTICATE permission to the Cert-based User in the other Database(s)
      4. Grant any additional permissions needed and/or add User to any potential Database-level Roles
         

    Three examples of a Database-only context are:

    • When using Service Broker. The activation procedure runs in the context of the EXECUTE AS clause of the Queue, which can only be a User, and cannot see the Instance-level even if there is a corresponding Login with the same SID.
    • When impersonating a User. This is the case when either the EXECUTE AS clause, or the EXECUTE AS USER statement, is used.
    • When using an Application Role / “AppRole”. After executing sp_setapprole, the Instance-level context can no longer be used, even though error messages will refer to the Login.
    • When setting a T-SQL Job Step of a SQL Server Agent job to "Execute as User"
    • Logging directly into a Contained database
       
  4. When required:
    1. Service Broker queues — When specifying an Activation Procedure, EXECUTE AS is required, and CALLER is not an available option. (CREATE QUEUE)
    2. Natively compiled stored procedures (and maybe scalar user-defined functions?) in SQL Server 2014 — The documentation states that EXECUTE AS CALLER was not supported in SQL Server 2014. This is no longer an issue starting with SQL Server 2016.
    3. Database-level and Server-level (i.e. Instance-level) Triggers — DDL and Logon triggers cannot be signed, unfortunately (please vote for: “Allow signing Database DDL Triggers and Server DDL and Logon Triggers – ADD SIGNATURE”). In these cases, you can use EXECUTE AS, or have the trigger execute a stored procedure that is properly signed (in which case, depending on what you are doing, you may need to pass along the output from EVENTDATA to that signed stored procedure).

      Please note: Database-level DDL triggers can technically be signed, but this is undocumented / unsupported behavior and can only be accomplished by signing the database, which is also undocumented behavior.


 

TRUSTWORTHY

The ONLY valid use of setting a Database to TRUSTWORTHY ON is for development and testing. That’s it.

This might be the easier option to implement, but it is never the only option available. Therefore, there is never any reason to enable TRUSTWORTHY for a Database in production.

Despite what you might think, or have read, or have been told, TRUSTWORTHY ON is not required for:

  1. Gaining Instance-level permissions
  2. Accessing another Database
  3. Loading a custom SQLCLR Assembly (whether prior to, or as of, SQL Server 2017)
  4. Loading an existing, unsigned (most likely SAFE) Assembly into SQL Server 2017 (or newer) from a pre-SQL Server 2017 Instance
  5. Loading an unsupported .NET Framework library (seriously!)
  6. or any other situation I have ever encountered, either personally or via answering someone’s question


 
 

Examples

Below are several step-by-step guides that I have written. More links will be added over time.

Instance-level Permissions

  1. Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level (post here on Sql Quantum Leap)

Database-level Permissions

  1. Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Database-level (post here on Sql Quantum Leap)

  2. Proc Inserts via Dynamic SQL into Table with Trigger that Inserts into Other Table (self-guided demo script on PasteBin.com)

Cross-Database Access

  1. How to grant execute permissions to a stored procedure but not the underlying databases (answer on DBA.StackExchange)

  2. Can’t use msdb.dbo.sp_send_dbmail when in service broker – executes as guest? (answer on DBA.StackExchange)


 
 

Conclusion

Starting with the release of SQL Server 2005, it is no longer necessary to use Cross-Database Ownership Chaining, Impersonation, or TRUSTWORTHY to grant additional permissions and/or perform cross-Database functionality. Those mechanisms existed because there was no other way. But, Module Signing, introduced in SQL Server 2005, uses Certificates (or Asymmetric Keys) to overcome all of the deficiencies of those other methods.

Also, the difference in effort-level between the two approaches is far less than what most people believe. Module Signing seems to have a somewhat bad reputation of being too complicated. And to be fair, when I first started using Module Signing it took a little while to fully comprehend it because it is not terribly intuitive. But, the problem is mostly due to a lack of documentation and explanation, a situation that I am trying to address via posts like this one and the presentation noted at the top of this post, etc.

So please do not be scared off by whatever preconceived notions you might have about complexity or effort-level regarding Module Signing. Try implementing it once or twice and it will begin to make sense rather quickly. The benefits of Module Signing are well worth a few extra minutes spent on a few extra steps!

For more information (including links to many examples of actual, non-demo applications of this approach), please visit: ModuleSigning.info

Post Update History
  • 2017-12-30 @ 11:50 ET / 2017-12-30 @ 16:50 UTC — Initial posting
  • 2021-11-12 @ 11:45 ET / 2021-11-12 @ 16:45 UTC — Added information and links

Exit mobile version