Use Master;
--  Create the certificate in the Master db
CREATE CERTIFICATE permissions_test
   ENCRYPTION BY PASSWORD = 'W0bJr&xQ4eGsaFJ4WMVWcy'
      WITH SUBJECT = 'Test signing stored proc to create server login', 
      EXPIRY_DATE = '01/01/2025';
go

-- Create login and assign Alter Any Login permissions
CREATE LOGIN permissions_test_Login FROM CERTIFICATE permissions_test
go

Grant ALTER ANY LOGIN  TO permissions_test_Login
go

--  Export Certificate
BACKUP CERTIFICATE permissions_test TO FILE = 'C:\temp\permissions_test.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\permissions_test.pvk' ,
                  ENCRYPTION BY PASSWORD = 'BackupKey123',
                  DECRYPTION BY PASSWORD = 'W0bJr&xQ4eGsaFJ4WMVWcy');
go

CREATE DATABASE permissions_test_db
go

Use permissions_test_db
go

--  Import Certificate
CREATE CERTIFICATE permissions_test FROM FILE = 'C:\temp\permissions_test.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\permissions_test.pvk',
                  DECRYPTION BY PASSWORD = 'BackupKey123',
                  ENCRYPTION BY PASSWORD = 'W0bJr&xQ4eGsaFJ4WMVWcy')
go

--  Clean up files 
EXEC xp_cmdshell 'DEL C:\temp\permissions_test.*'
go

--  In case we need database permissions too
CREATE USER permissions_test_User FOR LOGIN permissions_test_Login; 
go

--  Create procedure that tried to create a login using dynamic sql
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[Test_Create_Logon]
AS
BEGIN
	
	SET NOCOUNT ON;
	
	DECLARE @sql nvarchar(500)
	
	Select @sql = 'CREATE LOGIN AAAAAAAA WITH PASSWORD = ''Letmein123'''
	Exec sp_executesql @sql
    
END
go

--  Create a test user and grant execure permissions
CREATE USER execute_proc_user WITHOUT LOGIN
go

Grant Execute On [dbo].[Test_Create_Logon] To execute_proc_user
go

Execute as User = 'execute_proc_user';
go
select current_user
go
Execute [dbo].[Test_Create_Logon]
go
-- Fails with permission problem
--Msg 15247, Level 16, State 1, Line 1
--User does not have permission to perform this action.

revert
go
select current_user
go

ADD SIGNATURE TO [dbo].[Test_Create_Logon] BY CERTIFICATE permissions_test
    WITH PASSWORD = 'W0bJr&xQ4eGsaFJ4WMVWcy'
go

--	Test with the signature on the stored procedure
Execute as User = 'execute_proc_user';
go
select current_user
go
Execute [dbo].[Test_Create_Logon]
go
--	Still no permissions to execute.....
--Msg 15247, Level 16, State 1, Line 1
--User does not have permission to perform this action.
revert
select current_user

Drop User execute_proc_user
go
Drop PROCEDURE [dbo].[Test_Create_Logon]
go
Drop User permissions_test_User
go
Drop CERTIFICATE permissions_test
go

Use Master
Go
Drop Login permissions_test_Login
go
Drop CERTIFICATE permissions_test
go
Drop DATABASE permissions_test_db

Use Master;
go

--  Create a Server Login, this will be the login that executes the stored procedure
CREATE LOGIN execute_proc_login  WITH PASSWORD = 'tmrRaYP2nXVNhUJMw&z766vO9v4A1Ra3dRDuBcjkDd'
go

--  Create our test database
CREATE DATABASE permissions_test_db
go

--  Create the certificate in the Master db
CREATE CERTIFICATE permissions_test
   ENCRYPTION BY PASSWORD = 'W0bJr&xQ4eGsaFJ4WMVWcy'
      WITH SUBJECT = 'Test signing stored proc to create server login', 
      EXPIRY_DATE = '01/01/2025';
go

-- Create login and assign Alter Any Login permissions
CREATE LOGIN permissions_test_Login FROM CERTIFICATE permissions_test
go

Grant ALTER ANY LOGIN  TO permissions_test_Login
go

--  Export Certificate
BACKUP CERTIFICATE permissions_test TO FILE = 'C:\temp\permissions_test.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\permissions_test.pvk' ,
                  ENCRYPTION BY PASSWORD = 'BackupKey123',
                  DECRYPTION BY PASSWORD = 'W0bJr&xQ4eGsaFJ4WMVWcy');
go


--  Switch to test database
Use permissions_test_db
go

--  Create a user in the database from the login that will run the sp
CREATE USER execute_proc_user For LOGIN execute_proc_login
go

--  Create procedure that tries to create a login using dynamic sql
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[Test_Create_Logon]
AS
BEGIN
        
        SET NOCOUNT ON;
        
        DECLARE @sql nvarchar(500)
        
        Select @sql = 'CREATE LOGIN AAAAAAAA WITH PASSWORD = ''Letmein123'''
        Exec sp_executesql @sql
    
END
go

--  Grant permissions to execute the stored procedure
Grant Execute On [dbo].[Test_Create_Logon] To execute_proc_user
go

--  Import Certificate
CREATE CERTIFICATE permissions_test FROM FILE = 'C:\temp\permissions_test.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\permissions_test.pvk',
                  DECRYPTION BY PASSWORD = 'BackupKey123',
                  ENCRYPTION BY PASSWORD = 'W0bJr&xQ4eGsaFJ4WMVWcy')
go

--  Clean up files 
EXEC xp_cmdshell 'DEL C:\temp\permissions_test.*'
go

--  In case we need database permissions too, eg. Grant select on schema......
CREATE USER permissions_test_user FOR CERTIFICATE permissions_test
go

ADD SIGNATURE TO [dbo].[Test_Create_Logon] BY CERTIFICATE permissions_test
    WITH PASSWORD = 'W0bJr&xQ4eGsaFJ4WMVWcy'
go


Execute as Login = 'execute_proc_login';
go
select current_user
go
Execute [dbo].[Test_Create_Logon]
go
--  This time no error!!!!!!

revert
go
Select current_user
go
Drop User execute_proc_user
go
Drop PROCEDURE [dbo].[Test_Create_Logon]
go
Drop User permissions_test_User
go
Drop CERTIFICATE permissions_test
go

Use Master
Go
Drop Login permissions_test_Login
go
Drop LOGIN execute_proc_login
go
Drop LOGIN AAAAAAAA -- Need to clean this up too now 
go
Drop CERTIFICATE permissions_test
go
Drop DATABASE permissions_test_db