/*~
    ================================================================
    DEMO: Certificate signing to facilitate credential rotation
    ================================================================
~*/
USE master;
GO
-- ------------------------------------------------------------
-- 1. Create a mock application login
-- ------------------------------------------------------------
CREATE LOGIN ApplicationAPI WITH PASSWORD = 'Str0ngInitial!Pwd';
GO
-- ------------------------------------------------------------
-- 2. Create a mock login for our DBA individual
-- ------------------------------------------------------------
CREATE LOGIN ImARealDBA WITH PASSWORD = 'AnotherStr0ngPwd!';
CREATE USER ImARealDBA FOR LOGIN ImARealDBA;
GO
-- Create a role for the junior DBA
CREATE ROLE Role_JuniorDBA;
ALTER ROLE Role_JuniorDBA ADD MEMBER ImARealDBA;
GO
-- ------------------------------------------------------------
-- 3. Create the stored procedure that performs the password
--    rotation
-- ------------------------------------------------------------
CREATE OR ALTER PROCEDURE dbo.usp_RotateLoginPassword
    @LoginName   SYSNAME,
    @NewPassword NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'ALTER LOGIN ' + QUOTENAME(@LoginName) +
               N' WITH PASSWORD = ' + QUOTENAME(@NewPassword, '''');
    EXEC sp_executesql @sql;
    PRINT 'Password successfully rotated for login: ' + QUOTENAME(@LoginName);
END;
GO
-- ------------------------------------------------------------
-- 4. Create a certificate and a certificate-based login that
--    holds the elevated permission (ALTER ANY LOGIN, CONTROL SERVER)
-- ------------------------------------------------------------
CREATE CERTIFICATE PasswordRotationCert
    ENCRYPTION BY PASSWORD = 'CertPassw0rd!'
    WITH SUBJECT = 'Certificate for signing password rotation SP';
GO
-- Create a login from the certificate
CREATE LOGIN Cert_PasswordRotation FROM CERTIFICATE PasswordRotationCert;
GO
-- Grant the elevated, normally-dangerous permission to the CERT LOGIN only
GRANT ALTER ANY LOGIN TO Cert_PasswordRotation;
GRANT CONTROL SERVER TO Cert_PasswordRotation
GO
-- ------------------------------------------------------------
-- 5. Sign the stored procedure with the certificate
--    This is the step that "packages" the elevated permissions
--    with the proc's execution context
-- ------------------------------------------------------------
ADD SIGNATURE TO dbo.usp_RotateLoginPassword
    BY CERTIFICATE PasswordRotationCert
    WITH PASSWORD = 'CertPassw0rd!';
GO
-- ------------------------------------------------------------
-- 6. Grant EXECUTE on the proc to the Junior DBA role (not the
--    underlying ALTER ANY LOGIN or CONTROL SERVER permission)
-- ------------------------------------------------------------
GRANT EXECUTE ON dbo.usp_RotateLoginPassword TO Role_JuniorDBA;
GO
/*~ 
  ================================================================
   TEST: Impersonate SupportUser and rotate the password
   ================================================================
~*/
EXECUTE AS LOGIN = 'ImARealDBA';
-- Confirm ImARealDBA does NOT have ALTER ANY LOGIN directly
SELECT HAS_PERMS_BY_NAME(NULL, NULL, 'ALTER ANY LOGIN') AS DirectPermission;
-- Expect: 0
-- But CAN successfully execute the signed procedure
EXEC dbo.usp_RotateLoginPassword
    @LoginName = 'ApplicationAPI',
    @NewPassword = 'BrandNewR0tatedPwd!';
REVERT;
GO
/*~
================================================================
   CLEANUP
================================================================
DROP PROCEDURE IF EXISTS dbo.usp_RotateLoginPassword;
DROP LOGIN Cert_PasswordRotation;
DROP CERTIFICATE PasswordRotationCert;
DROP ROLE Role_JuniorDBA;
DROP USER ImARealDBA;
DROP LOGIN ImARealDBA;
DROP LOGIN ApplicationAPI;
================================================================ 
~*/