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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityCREATE 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.
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.
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];
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!!