In this post, Data Education founder and trainer Adam Machanic takes things one step further in regards to assigning permissions. Here, he explains that signing a stored procedure can also be used to propagate server-level permissions.

The previous post showed only how to assign database-level permissions using a certificate. Signing a stored procedure can also be used to propagate sever-level permissions, such as BULK INSERT or ALTER DATABASE. Doing so requires creation of a proxy login from a certificate, followed by creation of a database user using the same certificate. To accomplish this, the certificate must be backed up after being created, and restored in the database in which you are creating the user. Once the database user is created, the procedure to apply permissions is the same as when propagating database-level permissions.

To begin with, create a certificate in the master database. Unlike previous examples, this certificate must include a password in its definition, in order to encrypt its private key. Once the certificate has been created, use it to create a proxy login:

CREATE CERTIFICATE alter_db_certificate
   ENCRYPTION BY PASSWORD = 'stR()Ng_PaSSWoRDs are?BeST!'
   WITH SUBJECT = 'ALTER DATABASE permission'
GO

CREATE LOGIN alter_db_login FROM CERTIFICATE alter_db_certificate
GO

This login, in case you can’t tell from the name, will be used to propagate ALTER DATABASE permissions. The next step is to grant the appropriate permissions to the login:

GRANT ALTER ANY DATABASE TO alter_db_login

At this point, the next step required is to back up the certificate to a file. The certificate can then be restored from the file into the database of your choosing, and from there can be used to create a database user that will have the same permissions as the server login, by virtue of having been created using the same certificate.

BACKUP CERTIFICATE alter_db_certificate
TO FILE = 'C:\alter_db.cer'
WITH PRIVATE KEY
(
    FILE = 'C:\alter_db.pvk',
    ENCRYPTION BY PASSWORD = 'an0tHeR$tRoNGpaSSWoRd?',
    DECRYPTION BY PASSWORD = 'stR()Ng_PaSSWoRDs are?BeST!'
)

Once backed up, the certificate can be restored in a database. For the purpose of this example, a new database can be created and used to keep things simple:

CREATE DATABASE alter_db_example
GO

USE alter_db_example
GO

CREATE CERTIFICATE alter_db_certificate
FROM FILE = 'C:\alter_db.cer'
WITH PRIVATE KEY
(
    FILE = 'C:\alter_db.pvk',
    DECRYPTION BY PASSWORD = 'an0tHeR$tRoNGpaSSWoRd?',
    ENCRYPTION BY PASSWORD = 'stR()Ng_PaSSWoRDs are?BeST!'
)
GO

It is worth noting that at this point, the certificate’s physical file should probably be either deleted or backed up to a safe storage repository. Although the private key is encrypted with the password, it would certainly be possible for a dedicated attacker to crack it via brute force. And since the certificate is being used to grant ALTER DATABASE permissions, such an attack could potentially end in some damage being done—so play it safe with these files.

After the certificate has been created in the database, the rest of the process is just as before. Create a stored procedure that requires the privilege escalation, create a user based on the certificate, and sign the stored procedure with the certificate:

CREATE PROCEDURE SetMultiUser
AS
BEGIN
    ALTER DATABASE alter_db_example
    SET MULTI_USER
END
GO

CREATE USER alter_db_user
FOR CERTIFICATE alter_db_certificate
GO

ADD SIGNATURE TO SetMultiUser
BY CERTIFICATE alter_db_certificate
WITH PASSWORD = 'stR()Ng_PaSSWoRDs are?BeST!'
GO

Finally, the permissions can be tested. In order for propagation of server-level permissions to work, the user executing the stored procedure must be associated with a valid server login, and the login must be impersonated rather than the user. So this time, CREATE USER WITHOUT LOGIN will not suffice:

CREATE LOGIN test_alter WITH PASSWORD = 'iWanT2ALTER!!'
GO

CREATE USER test_alter FOR LOGIN test_alter
GO

GRANT EXECUTE ON SetMultiUser TO test_alter
GO

Finally, the test_alter login can be impersonated, and the stored procedure executed:

EXECUTE AS LOGIN='test_alter'
GO

EXEC SetMultiUser
GO

This example was obviously quite simplistic, but it should serve as a basic template that you can adapt as necessary when you need to provide escalation of server-level privilege to database users.