Sql-server – SQL Server stored procedure permissions

The way that works best for me, to write stored procedures that execute dynamic SQL, is by not using "EXEC sp_executesql" when I start writing the stored procedure, but by using "PRINT" statements.

I make sure I get to see each and every SQL that the stored procedure generates. I copy them from the Messages tab and execute them from within Management Studio. Only after all SQLs work as intended, I allow my stored procedure to "EXEC sp_executesql" (instead of simply PRINTing the SQL statements).

Debugging works very fast this way. The most problems I encountered so far, are the result of missing spaces, like 'FROMMyTableName' or 'WHEREId = 12'.

You will have to test this as the certificate user or with an account that has similar rights. And if you can avoid using dynamic SQLs, avoid them.

See Signing an activated procedure for an example of how to properly sign an activated procedure exactly so it it can leverage VIEW SERVER STATE privilege from an activated procedure. The steps are:

• inspect the procedure code to ensure that you trust it
• change the procedure to have an EXECUTE AS OWNER clause (without EXECUTE AS, even if the module is signed, the principal will not have access outside the host database because of how Service Broker executes the activation procedure)
• create a certificate with a private key in your app database
• sign the procedure with the private key of the certificate you created
• drop the private key of the certificate (to prevent it from ever being used again)
• copy the certificate into the master database
• create a login from the certificate
• grant AUTHENTICATE SERVER to the certificate derived login
• grant any additional privilege required by the procedure (e.g. VIEW SERVER STATE) to the certificate derived login