A while back I wrote a post, Digitally Signing a Stored Procedure To Allow It To Run With Elevated Permissions

Recently Manish Kumar asked an interesting question, what do you do if your proc accesses multiple or even all the databases on the server?

So, instead of giving him a fuzzy answer in reply, I thought I’d write up exactly how you can deal with that sort of situation.

We’ve got two options and we’ll have a look at both of them (I’m not going to go into details about how signing procs works, please see the post mentioned earlier for a basic overview, here I’m going to look specifically at procs that access multiple databases).

Create the certificate and user on all databases

The first option is to import the certificate used to sign the procedure into all other databases.  You can then create a certificate user and assign it permissions in the same way that I described in the original post.

Let’s just look at an example where we’ve got a procedure in the SQLUndercover database that needs to access other databases on the sever.

We’ll create the certificate, user and sign the procedure.

--create certificate
CREATE CERTIFICATE InsertIntoTestDBs_cert
ENCRYPTION BY PASSWORD = 'P455w0rd'
WITH SUBJECT = 'Certificate to sign InsertIntoTestDBs Procedure'
GO
--create user
CREATE USER CertUser FROM CERTIFICATE InsertIntoTestDBs_cert
GO
--create proc
CREATE OR ALTER PROC InsertIntoTestDBs
AS
BEGIN
INSERT INTO Test1.dbo.Tab1
VALUES(RAND(),RAND())
INSERT INTO Test2.dbo.Tab1
VALUES(RAND(),RAND())
INSERT INTO Test3.dbo.Tab1
VALUES(RAND(),RAND())
END
GO
--sign proc
ADD SIGNATURE TO InsertIntoTestDBs
BY CERTIFICATE InsertIntoTestDBs_cert WITH PASSWORD = 'P455w0rd'

Now let’s try to execute that proc with an account with limited permissions…

Msg 916, Level 14, State 2, Procedure InsertIntoTestDBs, Line 6 [Batch Start Line 3]
The server principal “Bob” is not able to access the database “Test1” under the current security context.

So how do we go about granting permissions to our signed proc on those other databases?  What we can do is import our certificate and it’s private key into each database and create a certificate user with permissions.

The first thing that we’re going to need to do is export the certificate and it’s private key.  Don’t forget the private key, this isn’t going to work without it.

BACKUP CERTIFICATE InsertIntoTestDBs_cert TO FILE = 'C:\SQLBackups\Certs\InsertIntoTestDBs_cert.cer'
WITH PRIVATE KEY (FILE = 'C:\SQLBackups\Certs\InsertIntoTestDBs_cert.pvk', ENCRYPTION BY PASSWORD = 'P455w0rd', DECRYPTION BY PASSWORD = 'P455w0rd')

Now we want to import that certificate into all the databases that our proc needs to access.

--import certificate into Test1
USE Test1
GO
CREATE CERTIFICATE InsertIntoTestDBs_cert
FROM FILE = 'C:\SQLBackups\Certs\InsertIntoTestDBs_cert.cer'
WITH PRIVATE KEY (FILE = 'C:\SQLBackups\Certs\InsertIntoTestDBs_cert.pvk', ENCRYPTION BY PASSWORD = 'P455w0rd', DECRYPTION BY PASSWORD = 'P455w0rd')
GO
--import certificate into Test2
USE Test2
GO
CREATE CERTIFICATE InsertIntoTestDBs_cert
FROM FILE = 'C:\SQLBackups\Certs\InsertIntoTestDBs_cert.cer'
WITH PRIVATE KEY (FILE = 'C:\SQLBackups\Certs\InsertIntoTestDBs_cert.pvk', ENCRYPTION BY PASSWORD = 'P455w0rd', DECRYPTION BY PASSWORD = 'P455w0rd')
GO
--import certificate into Test3
USE Test3
GO
CREATE CERTIFICATE InsertIntoTestDBs_cert
FROM FILE = 'C:\SQLBackups\Certs\InsertIntoTestDBs_cert.cer'
WITH PRIVATE KEY (FILE = 'C:\SQLBackups\Certs\InsertIntoTestDBs_cert.pvk', ENCRYPTION BY PASSWORD = 'P455w0rd', DECRYPTION BY PASSWORD = 'P455w0rd')
GO

With the certificate now in place, all we need to do is create the certificate user and grant appropriate permissions.

--Create certificate user on Test1
USE Test1
GO
CREATE USER InsertIntoTestDBs_user FROM CERTIFICATE InsertIntoTestDBs_cert
GO
GRANT INSERT ON tab1 TO InsertIntoTestDBs_user
GO
--Create certificate user on Test2
USE Test2
GO
CREATE USER InsertIntoTestDBs_user FROM CERTIFICATE InsertIntoTestDBs_cert
GO
GRANT INSERT ON tab1 TO InsertIntoTestDBs_user
GO
--Create certificate user on Test3
USE Test3
GO
CREATE USER InsertIntoTestDBs_user FROM CERTIFICATE InsertIntoTestDBs_cert
GO
GRANT INSERT ON tab1 TO InsertIntoTestDBs_user
GO

With those created, let’s have a go at running the proc again using the same limited login.

(1 row affected)

(1 row affected)

(1 row affected)

Success!!!

Create the certificate in master and grant permissions certificate login

That’s all well and good, but what if you’ve got hundreds of databases and you’re finding the above approach too difficult to maintain?

There is another way that we can go about this that may be easier to manage, although in my opinion slightly less secure.

Instead of importing the certificate into every database individually and creating users, we could import it into the master database, create a certificate login and then grant that login permissions as required.  This means that we only ever have to import the certificate once into the master database and only have to create one login.  That login’s permissions can be managed in the same way that we’d manage the permissions of any login.

Follow the above example until the point that you’ve exported the certificate.

This time, we’re going to import the certificate into the master database.

USE Master
GO
CREATE CERTIFICATE InsertIntoTestDBs_cert
FROM FILE = 'C:\SQLBackups\Certs\InsertIntoTestDBs_cert.cer'
WITH PRIVATE KEY (FILE = 'C:\SQLBackups\Certs\InsertIntoTestDBs_cert.pvk', ENCRYPTION BY PASSWORD = 'P455w0rd', DECRYPTION BY PASSWORD = 'P455w0rd')

Once the certificate is in place, you can create a login associated with it.

CREATE LOGIN InsertIntoTestDBs_Login
FROM CERTIFICATE InsertIntoTestDBs_cert

With the login created you can now manage the permissions in the same way you would with any login, using your favourite tool, be that the SSMS GUI, via a script, DBATools or whatever else you may use.

USE [Test1]
GO
CREATE USER [InsertIntoTestDBs_Login] FOR LOGIN [InsertIntoTestDBs_Login]
GRANT INSERT ON tab1 TO InsertIntoTestDBs_Login
GO
USE [Test2]
GO
CREATE USER [InsertIntoTestDBs_Login] FOR LOGIN [InsertIntoTestDBs_Login]
GRANT INSERT ON tab1 TO InsertIntoTestDBs_Login
GO
USE [Test3]
GO
CREATE USER [InsertIntoTestDBs_Login] FOR LOGIN [InsertIntoTestDBs_Login]
GRANT INSERT ON tab1 TO InsertIntoTestDBs_Login
GO

or from the GUI

Run that proc now, with our limited login and we’ll see that it executes successfully.

Thanks for reading, hopefully you’ve found that useful.