SQL Server Permissions on Stored Procs with Dynamic SQL

SQL Server Permissions on Stored Procs with dynamic SQL

Yes.

Add an EXECUTE AS CALLER clause to the procedure, then sign the stored procedure and give the required permission to the signature. This is 100% safe, secure and bullet proof. See Signing Procedures with Certificates.

permission problem on dynamic query running in a stored procedure

The short answer is yes.

When you compile a stored procedure, permissions of the user/login creating the stored procedure are checked. When someone else executes it, their ability to read those tables is no longer relevant (in most cases), but rather just their ability to execute the SP.

When executing the dynamic code, however, the permissions regarding the tables have to be checked there and then. This means that the executing user's permissions are being checked.

Stored Procedure and Permissions - Is EXECUTE enough?

Execute permissions on the stored procedure is sufficient.

CREATE TABLE dbo.Temp(n int)

GO
DENY INSERT ON dbo.Temp TO <your role>
GO
CREATE PROCEDURE dbo.SPTemp(@Int int)
AS

INSERT dbo.Temp
SELECT  @Int 

GO

GRANT EXEC ON dbo.SPTemp TO <your role>

GO

Then the (non-db_owner) user will have the following rights:

EXEC dbo.SPTemp 10
GO

INSERT dbo.Temp --INSERT permission was denied on the object 'Temp'
SELECT  10

However, if there is dynamic SQL inside dbo.SPTemp that attempts to insert into dbo.Temp then that will fail. In this case direct permission on the table will need to be granted.

security permission differences between select and sp_executesql within stored procedure

First is why... is there a specific rationale as to why the
permissions are implied and cascaded with one, and not the other

The reason why permissions are not required with the static SQL statement inside the stored procedure is due to ownership chaining. When all the objects involved are owned by the same user, permissions are not checked on indirectly referenced objects. Users need only execute permissions on the stored proc.

Second is... is there a work around***?

Dynamic SQL effectively breaks the ownership chain. Workarounds that do not require one grant permissions to end users on the objects include, EXECUTE AS and signing the proc with a certificate associated with a user with the needed permissions.

Below is an example of the certificate method gleaned from this tutorial in the documentation. This creates an ephemeral certificate for the permissions needed by the proc. See Erland's article for a thorough discussion of the certificate technique.

CREATE CERTIFICATE YourStoredProcedureDynamicSqlCertificate
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
      WITH SUBJECT = 'Provide dynamic SQL permissions';   
GO
ADD SIGNATURE TO dbo.YourStoredProcedure
   BY CERTIFICATE YourStoredProcedureDynamicSqlCertificate 
    WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';  
GO  
CREATE USER YourStoredProcedureDynamicSqlCertificateUser 
   FROM CERTIFICATE YourStoredProcedureDynamicSqlCertificate;  
GO  
GRANT SELECT ON dbo.services TO YourStoredProcedureDynamicSqlCertificateUser;  
GRANT SELECT ON dbo.tickets TO YourStoredProcedureDynamicSqlCertificateUser;  
GO  
ALTER CERTIFICATE YourStoredProcedureDynamicSqlCertificate
    REMOVE PRIVATE KEY;
GO

SQL Server stored procedures and permissions with other databases

EXECUTE AS Owner is a database sandbox. Think about it, it has to be. Otherwise a database administrator can issue and EXECUTE AS USER = 'somesystemadmin' and elevate himself to an instance level administrator. The details are described in Extending Database Impersonation by Using EXECUTE AS:

when impersonating a principal by using the EXECUTE AS USER statement, or within a database-scoped module by using the EXECUTE AS clause, the scope of impersonation is restricted to the database by default. This means that references to objects outside the scope of the database will return an error.

The solution is simple: sign the procedure. See Call a procedure in another database from an activated procedure for an example. Read more at Module Signing and Signing Stored Procedures in SQL Server.

---

Related Topics