Using a digital signature as a secondary identity to replace Cross database ownership chaining

re: Using a digital signature as a secondary identity to replace Cross database ownership chaining

raulga — Tue, 18 Mar 2008 20:26:22 GMT

Corrected a minor bug regarding the description of CDOC.

re: Using a digital signature as a secondary identity to replace Cross database ownership chaining

RaviRAVI — Fri, 18 Apr 2008 22:01:55 GMT

Awesome article! I learned quite a lot from this article-

A caller needs just execute permissions on a digitally signed stored procedure.

If this stored procedure accesses a local database resource then a database user needs to be created FOR THE CERTIFICATE with necessary permissions on that resource. If this stored procedure accesses a server resource then a login needs to be created FOR THE CERTIFICATE with necessary permissions on that resource.

If this stored procedure accesses another database resource then also a login needs to be created FOR THE CERTIFICATE and a database user on the remote database with necessary permissions on that resource.

If this stored procedure accesses a server resource I guess I need to create a credential for the certificate login?

Ravi A

re: Using a digital signature as a secondary identity to replace Cross database ownership chaining

raulga — Tue, 22 Apr 2008 04:16:07 GMT

Unfortunately this solution doesn’t work for accessing a remote DB (i.e. linked servers). The certificate as a secondary identity is scoped only for a given SQL Server instance. A workaround in this case could be using EXECUTE AS LOGIN (or equivalent) with linked servers.

NOTE: SETUSER and approles are sandboxed to the current SQL Server instance and cannot be used with linked servers.

-Raul Garcia

SDE/T

SQL Server Engine