SQL Server Certificate to grant permissions on database

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’

Leave a Reply

  

  

  


6 − = 4