-- This script takes it base in the master database.
-- It permits you sign a procedure to bestow it with
-- server-level permissions. If the database is in an
-- AG, the certificate and login is created on all servers
-- in the AG.
-- This version is intended for SQL 2005 and SQL 2008 only. For
-- SQL 2012 and later, see GrantPermsToSP_server.sql.
USE master
go
-- Set up parameters: the procedure to sign and the database it belongs to.
DECLARE @database nvarchar(260),
        @procname nvarchar(520)

SELECT @database = 'Playground',
       @procname = 'Management.ShowSessions'

-- The permissions to grant through the certificate. Leave table empty
-- to only remove current permissions.
DECLARE @perms TABLE (perm nvarchar(400) NOT NULL PRIMARY KEY)
INSERT @perms VALUES
   ('VIEW SERVER STATE')

-- Run with debug or not?
-- CAVEAT! Always set to 0 when running in production, so that the 
-- CREATE CERTIFICATE with the private key is not displayed!
DECLARE @debug bit
SELECT @debug = 1

--============================ END OF SETUP ==========================
-- More local variables.
DECLARE @sp_executesql   nvarchar(350),
        @certname        sysname,
        @username        sysname,
        @subject         nvarchar(4000),
        @pwd             char(39),
        @filename        nvarchar(1024),
        @cmd             varchar(1024),
        @public_key      varchar(MAX),
        @sql             nvarchar(MAX),
        @server          sysname,
        @nl              char(2),
        @pvt             char(2),
        -- These are comments that we attach to the dynamic SQL to
        -- understand the output better.
        @mastercomment  nvarchar(200),
        @servercomment  nvarchar(200),
        @dbstart        nvarchar(200),
        @dbend          nvarchar(200)


SET XACT_ABORT ON

-- A big TRY-CATCH block around everything to abort on first error.
BEGIN TRY

SELECT @nl = char(13) + char(10)

-- First verify that the database exists.
IF db_id(@database) IS NULL
   RAISERROR('Database %s does not exist.', 16, 1, @database)

-- Make sure that database name is quoted and appears exactly as in
-- sys.databases. 
SELECT @database = quotename(name)
FROM   sys.databases
WHERE  name = @database

-- We will call sp_executesql a number of times in the target database.
SELECT @sp_executesql = @database + '.sys.sp_executesql'

-- Set up comments for local and the database. For the database, we also
-- an impersonation of dbo. This is a protection against a malicious power
-- user who have added a DDL trigger which is trying exploit the permission
-- of a sysadmin user who is running. By impersonating a database user, we
-- are sandboxed things in the database, and cannot perform things outside
-- of it. (Unless the database is TRUSTWORTHY and owned by sysadmin, but that
-- is a security issue in itself.)
SELECT @mastercomment = @nl + '-- In master' + @nl,
       @dbstart       = @nl + '-- In database ' + @database + @nl +
                             'EXECUTE AS USER = ''dbo''' + @nl,
       @dbend         = @nl + 'REVERT'

-- Next we verify that the procedure exists and make sure that we have a
-- normalised quoted name. We need to run a query in the target database.
-- The point with MIN is that we get NULL if the procedure is does not exist.
SELECT @sql = @dbstart +
    'SELECT @procname = MIN(quotename(s.name) + ''.'' + quotename(o.name))
     FROM   sys.objects o
     JOIN   sys.schemas s ON o.schema_id = s.schema_id
     WHERE  o.object_id = object_id(@procname)' + 
   @dbend
IF @debug = 1 PRINT @sql
EXEC @sp_executesql @sql, N'@procname nvarchar(260) OUTPUT', @procname OUTPUT

IF @procname IS NULL
   RAISERROR('No procedure with the given name in database %s.', 16, 1, @database)

-- Construct name and password for the certificate.
SELECT @certname = 'SIGN ' + @database + '.' + @procname,
       @pwd      = convert(char(36), newid()) + 'Aa0'

-- And construct the subject from the permissions.
SELECT @subject = 'GRANT ' +
      (SELECT CASE row_number() OVER (ORDER BY (SELECT NULL))
                  WHEN 1 THEN ''
                  ELSE ' - '
            END + perm
      FROM   @perms
      FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')

-- Maks sure that the subject is syntactically valid.
SELECT @subject = '"' + replace(@subject, '"', '""') + '"'

-- If a login exists for the cerficiate, we drop it.
IF EXISTS (SELECT *
           FROM   sys.server_principals
           WHERE  name = @certname
             AND  type = 'C')
BEGIN
   SELECT @sql = @mastercomment + 'DROP LOGIN ' + quotename(@certname, '"')
   IF @debug = 1 PRINT @sql
   EXEC (@sql)
END

-- And drop the certificate itself.
IF EXISTS (SELECT * FROM sys.certificates WHERE name = @certname)
BEGIN
   SELECT @sql = @mastercomment + 'DROP CERTIFICATE ' + quotename(@certname, '"')
   IF @debug = 1 PRINT @sql
   EXEC(@sql)
END

-- In the target database, we must remove the signature from the procedure,
-- so that we can drop the certificate.
SELECT @sql = @dbstart +
   'IF EXISTS (SELECT *
               FROM   sys.crypt_properties cp
               JOIN   sys.certificates c ON cp.thumbprint = c.thumbprint
               WHERE  cp.major_id = object_id(@procname)
                 AND  c.name = @certname)
        DROP SIGNATURE FROM ' + @procname +
           ' BY CERTIFICATE ' + quotename(@certname, '"') + 
   @dbend
IF @debug = 1 PRINT @sql
EXEC @sp_executesql @sql, N'@certname sysname, @procname nvarchar(260)',
                    @certname, @procname

-- No user should have been created from the cert, but if so, we drop it.
-- Since this may have been performed by some else, we cannot trust the
-- username to be the same as the certificate name.
SELECT @sql = @dbstart +
   'SELECT @username = NULL
    SELECT @username = dp.name
    FROM   sys.database_principals dp
    JOIN   sys.certificates c ON dp.sid = c.sid
    WHERE  c.name = @certname' + 
    @dbend
IF @debug = 1 PRINT @sql
EXEC @sp_executesql @sql, N'@certname  sysname, @username sysname OUTPUT',
                          @certname, @username OUTPUT

IF @username IS NOT NULL
BEGIN
   SELECT @sql = @dbstart + 'DROP USER ' + quotename(@username, '"') + @dbend
   IF @debug = 1 PRINT @sql
   EXEC @sp_executesql @sql
END

-- And here goes the old cert.
SELECT @sql = @dbstart +
   'IF EXISTS (SELECT * FROM sys.certificates WHERE name = @certname)
       DROP CERTIFICATE ' + quotename(@certname, '"') + 
   @dbend
IF @debug = 1 PRINT @sql
EXEC @sp_executesql @sql, N'@certname  sysname', @certname

IF EXISTS (SELECT * FROM @perms)
BEGIN
   -- Now we start to (re)create things. First create the certificate in
   -- in the target database.
   SELECT @sql = @dbstart +
      'CREATE CERTIFICATE ' + quotename(@certname, '"') + '
       ENCRYPTION BY PASSWORD = ' + quotename(@pwd, '''') + '
       WITH SUBJECT = ' + quotename(@subject, '''') +
      @dbend
   IF @debug = 1 PRINT @sql
   EXEC @sp_executesql @sql

   -- Sign the procedure.
   SELECT @sql = @dbstart +
       'ADD SIGNATURE TO ' + @procname + ' BY CERTIFICATE ' + quotename(@certname, '"') + '
         WITH PASSWORD = ' + quotename(@pwd, '''') +
       @dbend
   IF @debug = 1 PRINT @sql
   EXEC @sp_executesql @sql

   -- Drop the private key.
   SELECT @sql = @dbstart + 
                 'ALTER CERTIFICATE ' + quotename(@certname, '"') + ' REMOVE PRIVATE KEY' + 
                 @dbend
   IF @debug = 1 PRINT @sql
   EXEC @sp_executesql @sql

   -- Make sure that the private key is really gone.
   SELECT @sql = @dbstart + 
       'SELECT @pvt = pvt_key_encryption_type FROM sys.certificates WHERE name = @certname' + 
       @dbend
   IF @debug = 1 PRINT @sql
   EXEC @sp_executesql @sql, N'@certname sysname, @pvt char(2) OUTPUT', @certname, @pvt OUTPUT
   IF isnull(@pvt, '') <> 'NA'
      RAISERROR('Private key for %s not dropped as expected.', 16, 1, @certname)

   -- Determine a path to where we can write the file for the cert.
   SELECT @filename = substring(physical_name, 1, len(physical_name) -
                                charindex('\', reverse(physical_name)) + 1) +
                      convert(char(36), newid()) + '.cer'
   FROM   sys.database_files
   WHERE  file_id = 1

   -- Backup the public key to disk
   SELECT @sql = @dbstart + 
      'BACKUP CERTIFICATE ' + quotename(@certname) + ' TO FILE = ' + quotename(@filename, '''') + 
      @dbend
   IF @debug = 1 PRINT @sql
   EXEC @sp_executesql @sql

   -- Create the certificate to master.
   SELECT @sql = @mastercomment +
      'CREATE CERTIFICATE ' + quotename(@certname, '"') + '
       FROM FILE = ' + quotename(@filename, '''')
   IF @debug = 1 PRINT convert(varchar(MAX), @sql)
   EXEC (@sql)

   -- If possible, delete the certificate from disk.
   SELECT @cmd = 'DEL "' + @filename
   IF (SELECT value_in_use
       FROM   sys.configurations
       WHERE  name = 'xp_cmdshell') = 1
   BEGIN
      IF @debug = 1 PRINT @cmd
      EXEC xp_cmdshell @cmd, 'no_output'
   END
   ELSE
   BEGIN
      PRINT @nl + '******** xp_cmdshell disabled, you need run this command manually'
      PRINT @cmd
   END

   -- Create a login for the certificate.
   SELECT @sql = @mastercomment +
                 'CREATE LOGIN ' + quotename(@certname, '"') + 
                    ' FROM CERTIFICATE ' + quotename(@certname, '"')
   IF @debug = 1 PRINT @sql
   EXEC(@sql)

   -- Create commands to grant permissions or add membership. 
   SELECT @sql = @mastercomment + 
      (SELECT CASE WHEN EXISTS (SELECT *
                                FROM   sys.server_principals dp
                                WHERE  dp.name = p.perm
                                  AND  dp.type = 'R')
                   THEN 'EXEC sp_addsrvrolemember ' + quotename(@certname, '''') + ', ' +
                              quotename(p.perm, '''')
                   ELSE 'GRANT ' + p.perm + ' TO ' + quotename(@certname, '"')
              END + @nl
       FROM   @perms p
       FOR    XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
   IF @debug = 1 PRINT @sql
   EXEC(@sql)
END

END TRY
BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   -- In cases there is an error while we are impersonating dbo, we need
   -- revert back, which must be done in the user database.
   IF EXISTS (SELECT * FROM sys.login_token WHERE usage = 'DENY ONLY')
      EXEC @sp_executesql N'REVERT'
   DECLARE @errmsg   nvarchar(2048),
           @severity int
   SELECT @severity = error_severity()
   SELECT @errmsg = 'Error ' + convert(varchar, error_number()) + ': ' + error_message()
   RAISERROR('%s', @severity, 1, @errmsg)
END CATCH