Grant permission using sql server certificate to execute system stored procedure like sp_updatestats. Sql server certificate is helpful to provide access to different users to execute certain stored procedure which require exclusive privilege after valid authentication method.
Example
1. Create a stored procedure to execute sp_updatestats
USE [master]
GO
CREATE PROCEDURE [dbo].[sp_updatestats_user]
AS
BEGIN
exec sp_updatestats
END
2. Grant execute permission to user
grant execute on [sp_updatestats_user] to [DOMAIN\DEV]
3. Now create server side certificate with encrypted with password
CREATE CERTIFICATE updatestats
ENCRYPTION BY PASSWORD = ‘Update@123’
WITH SUBJECT = ‘Certificate for updatestats’,
START_DATE = ‘20140212’, EXPIRY_DATE = ‘20151231’
4. Create login for server side certificate
CREATE LOGIN updatestats FROM CERTIFICATE updatestats
GO
5. Grant sysadmin role to execute system stored procedure by executing below sql statement.
EXEC master..sp_addsrvrolemember @loginame = N’updatestats’, @rolename
=N’sysadmin’
GO
6. Add signature to user by using certificate
ADD SIGNATURE TO [sp_updatestats_user] BY CERTIFICATE updatestats
WITH PASSWORD = ‘Update@123’