Server role with permission only to create server login, not alter one

Question

I am working with Microsoft SQL Server Management Studio and I want to create a server role that has permissions to create server login but only create, not alter!

Does anyone know what is the permissions name?

Answer 1

CREATE and ALTER are tied together into ALTER ANY. What is the actual goal? If you're going to give a person the rights to create logins, why block from alter? Whatever you think they might try to do, they could just create an additional login instead of changing an existing one, or drop/create the original.

You could possibly block ALTER LOGIN commands via a DDL trigger depending on the caller, but that is quite different from permissions.

Answer 2

While I am not sure of the practicality of such a setup, it is, nonetheless, possible to do this (i.e. CREATE LOGIN only, no DROP LOGIN and no ALTER LOGIN). You can even get it to be a role (as requested), though not a built-in server role.

As @Aaron mentioned, there is no specific permission for CREATE LOGIN; there is only ALTER ANY LOGIN that covers the create, alter, and drop. So, the trick is to use a Certificate and Module Signing to (effectively) pick just the one permission that you want.

In this approach we will create a Certificate, and then a Login based on that Certificate, and then we will grant that Login the ALTER ANY LOGIN permission. But the fact that this Certificate-based Login can also alter and drop Logins is irrelevant since we will also create a Stored Procedure that uses Dynamic SQL to do the CREATE LOGIN and then sign that Stored Procedure with the same Certificate that was used to create the Login. The Certificate being the same for the Login as well as the Module Signing will link the Login and the Stored Procedure, giving the code in that Stored Procedure the potential ability to create, alter, and drop Logins, but if the code in the Stored Procedure is only written to execute the CREATE LOGIN, then that is all that can be done.

The final piece is to create a Database Role, grant the Role EXECUTE on the Stored Procedure, and finally add whatever User(s) you want to that Database Role.

SETUP

Step 1: Create the Stored Procedure and Database Role

USE [tempdb];

IF (OBJECT_ID(N'dbo.CreateLogin') IS NULL)
BEGIN
    EXEC(N'CREATE PROCEDURE dbo.CreateLogin AS SET NOCOUNT ON;');
END;
GO
ALTER PROCEDURE dbo.CreateLogin
(
    @Login sysname,
    @Password NVARCHAR(500),
    @DebugMode BIT = 0
)
AS
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX);

SET @SQL =  N'CREATE LOGIN '
            + QUOTENAME(@Login)
            + N' WITH PASSWORD = N'''
            + @Password + N''';';

IF (@DebugMode = 0)
BEGIN
  EXEC (@SQL);
END;
ELSE
BEGIN
  PRINT @SQL;
END;
GO
---

CREATE ROLE [LoginCreator];

GRANT EXECUTE ON [dbo].[CreateLogin] TO [LoginCreator];

Step 2: Create the Certificate and Login, Grant the Permission, Copy the Certificate to the Target DB, and Sign the Stored Procedure

EXEC (N'
USE [master];

IF (CERT_ID(N''Permission:AlterAnyLogin$Cert'') IS NULL)
BEGIN
  PRINT ''Creating Certificate in [master]...'';
  CREATE CERTIFICATE [Permission:AlterAnyLogin$Cert]
    ENCRYPTION BY PASSWORD = N''MyCertificate!MineMineMine!''
    WITH SUBJECT = N''Grant the ALTER ANY LOGIN permission'',
    EXPIRY_DATE = ''2099-12-31'';

  PRINT ''Creating Certificate-based Login...'';
  CREATE LOGIN [Permission:AlterAnyLogin$User]
    FROM CERTIFICATE [Permission:AlterAnyLogin$Cert];
END;

GRANT ALTER ANY LOGIN TO [Permission:AlterAnyLogin$User];
');


DECLARE @CertificateBytes VARBINARY(MAX),
        @PrivateKeyBytes VARBINARY(MAX),
        @SQL NVARCHAR(MAX);

EXEC dbo.sp_executesql
  N'
USE [master];

DECLARE @CertID INT;
SET @CertID = CERT_ID(N''Permission:AlterAnyLogin$Cert'');
SELECT @tmpCertificateBytes = CERTENCODED(@CertID),
       @tmpPrivateKeyBytes = CERTPRIVATEKEY(@CertID, N''MyCertificate!MineMineMine!'',
                                            N''MyCertificate!MineMineMine!'');
',
 N'@tmpCertificateBytes VARBINARY(MAX) OUTPUT, @tmpPrivateKeyBytes VARBINARY(MAX) OUTPUT',
  @tmpCertificateBytes = @CertificateBytes OUTPUT,
  @tmpPrivateKeyBytes = @PrivateKeyBytes OUTPUT;

USE [tempdb];
SET @SQL = N'CREATE CERTIFICATE [Permission:AlterAnyLogin$Cert] FROM BINARY = '
            + CONVERT(NVARCHAR(MAX), @CertificateBytes, 1)
            + N' WITH PRIVATE KEY ( BINARY = '
            + CONVERT(NVARCHAR(MAX), @PrivateKeyBytes, 1)
            + N', DECRYPTION BY PASSWORD = N''MyCertificate!MineMineMine!'''
            + N', ENCRYPTION BY PASSWORD = N''MyCertificate!MineMineMine!'' );';
PRINT @SQL;
EXEC(@SQL);

ADD SIGNATURE
  TO [dbo].[CreateLogin]
  BY CERTIFICATE [Permission:AlterAnyLogin$Cert]
  WITH PASSWORD = N'MyCertificate!MineMineMine!';

Step 3: Create a test Login and User, and add User to the Database Role

IF (SUSER_ID(N'JohnnyLunchbucket') IS NULL)
BEGIN
  PRINT 'Creating Login: [JohnnyLunchbucket] ...';
  CREATE LOGIN [JohnnyLunchbucket] WITH PASSWORD = 'WeakPasswurd ;-)';
END;

CREATE USER [JohnnyLunchbucket] FOR LOGIN [JohnnyLunchbucket];

ALTER ROLE [LoginCreator] ADD MEMBER [JohnnyLunchbucket];

TESTS

Test 1: As a privileged Login / User, verify the total number of Logins and that the test Login doesn't exist

SELECT COUNT(*) FROM sys.server_principals; -- 54 (or whatever)
SELECT * FROM sys.server_principals WHERE [name] = N'TempLogin'; -- no rows

Test 2: As the low-privileged test Login / User, verify the total number of Logins and attempt to CREATE LOGIN

EXECUTE AS LOGIN = N'JohnnyLunchbucket';
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];

SELECT COUNT(*) FROM sys.server_principals; -- 11 (much lower than before)

CREATE LOGIN [TempLogin] WITH PASSWORD = N'Sup,Yo?!';
/*
Msg 15247, Level 16, State 1, Line xxxxx
User does not have permission to perform this action.
*/

Test 3: As the low-privileged test Login / User, Execute the Stored Procedure

EXECUTE dbo.CreateLogin N'TempLogin', N'Sup,Yo?!'; -- SUCCESS!!!!!

Test 4: As the low-privileged test Login / User, check the total number of Logins and attempt to ALTER LOGIN

SELECT COUNT(*) FROM sys.server_principals; -- still 11

ALTER LOGIN [TempLogin] ENABLE;
/*
Msg 15151, Level 16, State 1, Line xxxxx
Cannot alter the login 'TempLogin', because it does not exist or you do not have permission.
*/

Test 5: As the privileged test Login / User, check the total number of Logins and attempt to ALTER LOGIN

REVERT;
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];

ALTER LOGIN [TempLogin] ENABLE; -- Success (so it does exist)

SELECT COUNT(*) FROM sys.server_principals; -- 55 (1 more than before)
SELECT * FROM sys.server_principals WHERE [name] = N'TempLogin'; -- Now it's there!!