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
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 CERTIFICATECertificateElevatePerms
ENCRYPTION BY PASSWORD='Walt3rZ3nga'
WITH SUBJECT='General cert to be used for processes that need a higher perms';
GO
GO
CREATE CERTIFICATECertificateElevatePerms
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';
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
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
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]GOALTER ROLE [db_owner] DROPMEMBER [iain]GOGRANT SELECT ON [HumanResources].[JobCandidate] TO [iain]GO
