Using Logins and Certificates to evelate Permissions

Problem

A common question I get asked by SQL developers is to be able to query the sys.dm_db_index_usage_stats table to see what indexes are being used. As happy as I am that SQL developers are taking an interest in their deployments, this requires the VIEW SERVER STATE permission which opens up a lot of DMVs and can be a potential security concern. This scenario is ideal for creating a login mapped to a certificate which will allow the SQL developer access to the DMV without directly granting elevated permissions.

Solution

The following steps outline how to create a stored procedure that can be executed by users to return index usage stats results for the database they are accessing.

Create a Stored Procedure

The Stored Procedure (sp) uses the db_id() function to only return results from the current database and joins with sys.indexes to make the results more readable. Creating the sp in master with a name that starts with sp_ allows the stored procedure to be called from any database.

IF EXISTS(SELECT 1 FROM sys.procedures WHERE name = 'sp_IndexUsageStats_PerDatabase')

BEGIN

DROP PROCEDURE sp_IndexUsageStats_PerDatabase

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROC sp_IndexUsageStats_PerDatabase

AS

BEGIN

SELECT db_name(database_id) as DatbaseName, object_name(ius.object_id) as   ObjectName,

        i.name, user_seeks, user_scans, user_lookups, user_updates,

        last_user_seek, last_user_scan, last_user_lookup, last_user_update

        FROM sys.dm_db_index_usage_stats ius

        INNER JOIN sys.indexes i

        ON i.index_id = ius.index_id AND i.object_id = ius.object_id

        where database_id = db_id()

END

If a user with limited privileges (for example, a user called iain who is part of the db_owner role in AdventureWorks2014) tries to execute the SP, the error that “The EXECUTE permission was denied on the object” will be returned.

This can be easily rectified by granting the execute permission on the SP to public...

USE master
GO
GRANT EXEC ON sp_IndexUsageStats_PerDatabase TO public
GO

which now changes the error message to...

Create a Certificate 

The certificate needs to be created in the master database.

USE master
GO
CREATE CERTIFICATE CertificateElevatePerms
ENCRYPTION BY PASSWORD = 'Walt3rZ3nga'
WITH SUBJECT = 'General cert to be used for processes that need a higher perms';
GO

Add a Signature

The signature ties the stored procedure to the certificate. Both need to be created before the signature can be set and if the stored procedure is altered, the signature needs to be reset. The password needs to be the same as the one used to create the certificate.

ADD SIGNATURE TO OBJECT ::sp_IndexUsageStats_PerDatabase
BY CERTIFICATE CertificateElevatePerms
WITH PASSWORD = 'Walt3rZ3nga' ;

Create a Login

A login which uses the certificate needs to be created next.

Books online describes a login mapped to a certificate as...

Logins created from certificates or asymmetric keys are used only for code signing. They cannot be used to connect to SQL Server. You can create a login from a certificate or asymmetric key only when the certificate or asymmetric key already exists in master.

USE master
GO
CREATE LOGIN CertificateElevatePerms_Login
FROM CERTIFICATE
GO

Grant the necessary permissions to the Login to be able to execute the SP

USE master
GO
GRANT VIEW SERVER STATE TO CertificateElevatePerms_Login ;
GO
GRANT AUTHENTICATE SERVER TO CertificateElevatePerms_Login ;
GO

Results

Running the same procedure as earlier now returns some results. Here the user has db_owner access to the AdventureWorks2014 database.

If a user only has access to specific objects rather than db_owner role (for example, just the JobCandidate table) then only the metadata for this object will be displayed.

USE [AdventureWorks2014]
GO
ALTER ROLE [db_owner] DROP MEMBER [iain]
GO
GRANT SELECT ON [HumanResources].[JobCandidate] TO [iain]
GO