Server Permissions in a Stored Procedure with a Certificate

  • Hi all,

    I'm just learning about signing stored procedures with certificates and I've been through multiple examples of how to set Server level permissions on a Stored Procedure. I'm really struggling to get Create Login permissions working. I'm sure it'll be something daft that I'm doing, but I can't see it at the minute and it's driving me crazy!

    The basic problem, is that I can create a Login/User with the correct privelages and execute the code as that login, and it works fine.

    If I create the login from a certificate from master database, grant him Alter Any Login permissions, export/import the certificate into my database and sign a stored procedure with the certificate, I have no permissions to create a login when executing the stored procedure with a database user. I've even tried in desperation elevating the certificate login to sysAdmin (on my development server of course) and still the permissions aren't filtering through.

    Here's some code to demonstrate the problem I have:

    --Set Up Environment

    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

    --Test the code without signing

    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.

    -- Test the code after signing

    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

    --Tidy up

    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

    Any help on where I'm going wrong would be really appreciated!!

    Can anyone tell me if they get a "User does not have permission to perform this action." after signing the certificate too?

  • Managed to fix this myself, if anyone else has the same problem here's the problem with my code.

    In order for the server permissions to work using certificates, you need to create database user from a login, and instead of Executing as User = .... you need to Execute as Login = .....

    Hey presto, no permissions problems.

    The code below works 🙂

    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

    Knew it would be something simple.........

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply