none
Signing a Stored Procedure to Create Logins

    Question

  • Hi

    Quick Question: How do you set up a certificate that allows a stored procedure to perform CREATE LOGIN?

    Long Version:

    My database needs to allow certain users (I have a database role called 'User_Manager' for them) permission to manage the user accounts via the FE. One of the needs is to be able to add new logins for new staff members.

    I've already set up a certificate that allows viewing and managing the roles assigned to users (plus safety checks in the procedures to ensure people can't lock themselves out of the system), but CREATE LOGIN is causing me a problem as it is a server level permission that is required and cannot be assigned to a database role, only actual logins.

    I tried to achieve this by creating a certificate in [master], then creating a server login from that certificate, granted the new login membership to securityadmin (I think this is safe since the login can only be used for signing) then copied the certificate from master into my database and finally signed the procedure with it.

    But it doesn't work, I receive the error 'user does not have permission to perform this action'.

    I can only assume I'm doing something wrong, but I cannot figure out exactly what.

    Any suggestions?

    Thanks

    Friday, May 10, 2013 8:10 AM

Answers

  • The script below works for me. What could be a problem is if you want to create a Windows login, as that requires contact with the AD, and I don't know which security context SQL Server uses for that.

    I used ALTER ANY LOGIN of laziness, but securityadmin should be the same.

    USE master
    go
    -- Create a test login.
    CREATE LOGIN testuser WITH PASSWORD = 'CeRT=0=TeST'
    go
    -- Create test database.
    CREATE DATABASE createlogintest
    go
    -- Create certificate in master.
    CREATE CERTIFICATE createlogincert
       ENCRYPTION BY PASSWORD = 'All you need is love'
       WITH SUBJECT = 'For CREATE LOGIN',
       START_DATE = '20020101', EXPIRY_DATE = '20200101'
    go
    -- Create a login for the certificate.
    CREATE LOGIN createlogincert_login FROM CERTIFICATE createlogincert
    go
    -- Grant rights for the certificate login.
    GRANT ALTER ANY LOGIN TO createlogincert_login
    go
    -- Save the certificate to disk.
    BACKUP CERTIFICATE createlogincert TO FILE = 'C:\temp\createlogincert.cer'
    WITH PRIVATE KEY (FILE = 'C:\temp\createlogincert.pvk' ,
                      ENCRYPTION BY PASSWORD = 'Tomorrow never knows',
                      DECRYPTION BY PASSWORD = 'All you need is love')
    go
    -- Move to test database.
    USE createlogintest
    go
    -- Create the non-priv user.
    CREATE USER testuser
    go
    -- Test procedure with BULK INSERT. BULK INSERT needs to be in
    -- EXEC() because of a bug in SQL Server.
    CREATE PROCEDURE create_login @name sysname AS
       DECLARE @sql nvarchar(MAX)
       SELECT @sql = 'CREATE LOGIN ' + quotename(@name) +
                     ' WITH PASSWORD = ' + quotename(convert(char(36), newid()), '''')
       EXEC(@sql)
    go
    -- Give test user right to execute the procedures.
    GRANT EXECUTE ON create_login TO testuser
    go
    -- Import the certificate we created in master into the test database.
    CREATE CERTIFICATE createlogincert FROM FILE = 'C:\temp\createlogincert.cer'
    WITH PRIVATE KEY (FILE = 'C:\temp\createlogincert.pvk',
                      DECRYPTION BY PASSWORD = 'Tomorrow never knows',
                      ENCRYPTION BY PASSWORD = 'A day in life')
    go
    -- Delete the files.
    EXEC master..xp_cmdshell 'DEL C:\temp\createlogincert.*', 'no_output'
    go
    -- Sign the test procedure.
    ADD SIGNATURE TO create_login BY CERTIFICATE createlogincert
        WITH PASSWORD = 'A day in life'
    go
    -- Switch to the test user.
    EXECUTE AS LOGIN = 'testuser'
    go
    -- Run procedure.
    EXEC create_login 'Amalia'
    go
    -- Back to ourselves.
    REVERT
    go
    -- Clean up.
    USE master
    go
    DROP DATABASE createlogintest
    DROP LOGIN createlogincert_login
    DROP CERTIFICATE createlogincert
    DROP LOGIN testuser
    DROP LOGIN Amalia
    EXEC xp_cmdshell 'DEL C:\temp\reloadtest.csv', 'no_output'


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 10, 2013 10:21 AM
  • If the issue is I suspect, sysadmin membership is not going to help, nor is impersonation with EXECUTE AS. The user who is logged in, must have the sufficient permissions in the AD on his own.

    However, this only speculation on my part. I am not in a position to test this myself, as I have a domain here at home, and my knowledge about Windows AD security is thin.

    One thing to try is to first make sure that you have a high-priv user that is able to run CREATE LOGIN. Then create a low-priv login, and grant that low-priv user IMPERSONATE permission on the high-priv user. Then run this:

    EXECUTE AS LOGIN = 'domain\highpriv'
    go
    CREATE LOGIN [domain\yetanotherlowpriv] FROM WINDOWS
    go
    REVERT

    You need to be able to freely add users to the AD to perform these test.

    Then again, why not just grant login to a Windows group, and have all membership administered that way:


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 10, 2013 11:58 AM
  • http://www.sommarskog.se/grantperm.html#Certificates

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Friday, May 10, 2013 8:58 AM

All replies

  • http://www.sommarskog.se/grantperm.html#Certificates

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Friday, May 10, 2013 8:58 AM
  • The script below works for me. What could be a problem is if you want to create a Windows login, as that requires contact with the AD, and I don't know which security context SQL Server uses for that.

    I used ALTER ANY LOGIN of laziness, but securityadmin should be the same.

    USE master
    go
    -- Create a test login.
    CREATE LOGIN testuser WITH PASSWORD = 'CeRT=0=TeST'
    go
    -- Create test database.
    CREATE DATABASE createlogintest
    go
    -- Create certificate in master.
    CREATE CERTIFICATE createlogincert
       ENCRYPTION BY PASSWORD = 'All you need is love'
       WITH SUBJECT = 'For CREATE LOGIN',
       START_DATE = '20020101', EXPIRY_DATE = '20200101'
    go
    -- Create a login for the certificate.
    CREATE LOGIN createlogincert_login FROM CERTIFICATE createlogincert
    go
    -- Grant rights for the certificate login.
    GRANT ALTER ANY LOGIN TO createlogincert_login
    go
    -- Save the certificate to disk.
    BACKUP CERTIFICATE createlogincert TO FILE = 'C:\temp\createlogincert.cer'
    WITH PRIVATE KEY (FILE = 'C:\temp\createlogincert.pvk' ,
                      ENCRYPTION BY PASSWORD = 'Tomorrow never knows',
                      DECRYPTION BY PASSWORD = 'All you need is love')
    go
    -- Move to test database.
    USE createlogintest
    go
    -- Create the non-priv user.
    CREATE USER testuser
    go
    -- Test procedure with BULK INSERT. BULK INSERT needs to be in
    -- EXEC() because of a bug in SQL Server.
    CREATE PROCEDURE create_login @name sysname AS
       DECLARE @sql nvarchar(MAX)
       SELECT @sql = 'CREATE LOGIN ' + quotename(@name) +
                     ' WITH PASSWORD = ' + quotename(convert(char(36), newid()), '''')
       EXEC(@sql)
    go
    -- Give test user right to execute the procedures.
    GRANT EXECUTE ON create_login TO testuser
    go
    -- Import the certificate we created in master into the test database.
    CREATE CERTIFICATE createlogincert FROM FILE = 'C:\temp\createlogincert.cer'
    WITH PRIVATE KEY (FILE = 'C:\temp\createlogincert.pvk',
                      DECRYPTION BY PASSWORD = 'Tomorrow never knows',
                      ENCRYPTION BY PASSWORD = 'A day in life')
    go
    -- Delete the files.
    EXEC master..xp_cmdshell 'DEL C:\temp\createlogincert.*', 'no_output'
    go
    -- Sign the test procedure.
    ADD SIGNATURE TO create_login BY CERTIFICATE createlogincert
        WITH PASSWORD = 'A day in life'
    go
    -- Switch to the test user.
    EXECUTE AS LOGIN = 'testuser'
    go
    -- Run procedure.
    EXEC create_login 'Amalia'
    go
    -- Back to ourselves.
    REVERT
    go
    -- Clean up.
    USE master
    go
    DROP DATABASE createlogintest
    DROP LOGIN createlogincert_login
    DROP CERTIFICATE createlogincert
    DROP LOGIN testuser
    DROP LOGIN Amalia
    EXEC xp_cmdshell 'DEL C:\temp\reloadtest.csv', 'no_output'


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 10, 2013 10:21 AM
  • Uri - I appreciate the link, but ironically that is the exact page I learnt about certificates and how to copy then between databases. Thanks for taking the time to reply though.

    Erland - You've hit the nail on the head there, I 'AM' trying to create windows logins, so that there must be the problem. When I get the chance I'll test it with the certificate login given sysadmin membership to see if works and then work backwards from there to see what I can find.

    I'll hold off marking an answer so I can share my findings.

    Friday, May 10, 2013 10:38 AM
  • If the issue is I suspect, sysadmin membership is not going to help, nor is impersonation with EXECUTE AS. The user who is logged in, must have the sufficient permissions in the AD on his own.

    However, this only speculation on my part. I am not in a position to test this myself, as I have a domain here at home, and my knowledge about Windows AD security is thin.

    One thing to try is to first make sure that you have a high-priv user that is able to run CREATE LOGIN. Then create a low-priv login, and grant that low-priv user IMPERSONATE permission on the high-priv user. Then run this:

    EXECUTE AS LOGIN = 'domain\highpriv'
    go
    CREATE LOGIN [domain\yetanotherlowpriv] FROM WINDOWS
    go
    REVERT

    You need to be able to freely add users to the AD to perform these test.

    Then again, why not just grant login to a Windows group, and have all membership administered that way:


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 10, 2013 11:58 AM
  • Well I've tested this again and it appears I must have made a mistake in how I was setting it up, signing a procedure using a certificate user with securityadmin membership can create windows logins.

    So from that I have made sure I have granted 'alter any login' to the certificate user and removed membership to securityadmin, and that is working as well.

    I'm stumped as to what I was doing wrong, but it's working now.

    Thanks for your help.

    Friday, May 10, 2013 1:26 PM