Sign Those Stored Procedures

David Fowler shows how we can sign stored procedures to give users limited rights that they otherwise should not have:

One way that we can do this is by signing the procedure (you can also use this with functions and triggers) with a certificate or an asymmetric key.

In this post I’m only going to look into signing a stored procedure with a certificate but the same ideas can be applied for the other objects and with an asymmetric key.

So…

We’re going to create a certificate and sign our stored proc using that certificate.  We’ll then create a user based on the certificate and grant the new certificate user the appropriate permissions to run the stored procedure.

Every SQL Server DBA should know how to do this, but in my experience, it’s a small percentage who do.

Related Posts

When MS_SSISServerCleanupJobLogin Is Orphaned

Sreekanth Bandarla noticed a problem in cleaning up SSIS metadata: Couple of weeks ago I was analyzing a server for space and noticed SSISDB database was abnormally huge (this Instance was running just a handful of packages). I noticed couple of internal schema tables in SSISDB were huge (with some hundreds of millions of rows), […]

Read More

The Key Hierarchy And SQL Server Encryption

David Fowler walks us through the various keys used in encrypting data in SQL Server: I’m sure that we all know that SQL Server includes all sorts of interesting functionality to allow us to encrypt our data and like with all encryption techniques, that data is encrypted using keys. In SQL Server we’ve got a […]

Read More

Categories

May 2018
MTWTFSS
« Apr Jun »
 123456
78910111213
14151617181920
21222324252627
28293031