Running SQL Server 2014 Express. Logged on as sa I try to execute the code below. It gives me the following error:
Msg 15247, Level 16, State 1, Line 25 User does not have permission to perform this action.
Why?! If I select SYSTEM_USER inside the procedure, it is indeed sa (the rightful owner).
USE [MyDatabase]
GO
CREATE PROCEDURE [dbo].[create_login]
@Login [nvarchar](256),
@Password [nvarchar](128)
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON
DECLARE @Sql NVARCHAR(4000)
SET @sql = N'CREATE LOGIN ' + QUOTENAME(@Login) + N' WITH PASSWORD = '
+ QUOTENAME(@Password, N'''') + N',CHECK_EXPIRATION=OFF, CHECK_POLICY=ON;'
EXEC (@sql)
END
GO
GRANT EXECUTE ON [dbo].[create_login] TO [my_simple_role]
GO
-- Let's go!
EXEC [dbo].[create_login] N'NewUser', N'c0Mpl3xP@55w0rd'
GO
-- Error! :(
If I run the statements outside the stored procedure, it works.
I'm trying to make a stored procedure that allows regular users to add server logins. The above does not seem to work. Please advise!
A login is a server level object and therefore requires server level permissions. EXECUTE AS <database-user> is a database-scoped security context.
One way to grant normal users privileged operations via stored procedures is by signing the module with a certificate that maps to a login that has the necessary rights. The necessary steps in this case are:
Below is an example gleaned from Erland Sommarskog's web site. Note that you will need to resign the proc with the cert each time it is altered.
--create database master key, if necessary
IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD='M@sterkEEPassw0rd';
END;
GO
CREATE CERTIFICATE SecurityAdministratorCertificate
WITH
SUBJECT = 'Allows non-privileged users to create and alter logins'
, START_DATE = '20020101'
, EXPIRY_DATE = '20300101';
GO
CREATE LOGIN SecurityAdministratorCertificateLogin
FROM CERTIFICATE SecurityAdministratorCertificate;
GO
GRANT ALTER ANY LOGIN TO SecurityAdministratorCertificateLogin;
GO
--export cert from master
DECLARE @CERTENC VARBINARY(MAX);
DECLARE @CERTPVK VARBINARY(MAX);
SELECT @CERTENC = CERTENCODED(CERT_ID(N'SecurityAdministratorCertificate'));
SELECT @CERTPVK = CERTPRIVATEKEY(CERT_ID(N'SecurityAdministratorCertificate'),
'All you need is love');
DECLARE @sql nvarchar(MAX);
SELECT @sql = N'CREATE CERTIFICATE SecurityAdministratorCertificate FROM BINARY = '
+ CONVERT(nvarchar(MAX), @CERTENC, 1)
+ ' WITH PRIVATE KEY ( BINARY = '
+ CONVERT(nvarchar(MAX), @CERTPVK, 1)
+ ', DECRYPTION BY PASSWORD = ''All you need is love'');'
--import cert into app databases
USE MyDatabase;
--create database master key, if necessary
IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD='M@sterkEEPassw0rd';
END;
EXEC(@sql);
GO
CREATE PROCEDURE [dbo].[create_login]
@Login [nvarchar](256),
@Password [nvarchar](128)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Sql NVARCHAR(4000);
SET @sql = N'CREATE LOGIN ' + QUOTENAME(@Login) + N' WITH PASSWORD = '
+ QUOTENAME(@Password, N'''') + N',CHECK_EXPIRATION=OFF, CHECK_POLICY=ON;';
EXEC (@sql);
END
GO
--grant exec permissions to users
GRANT EXECUTE ON [dbo].[create_login] TO [my_simple_role];
GO
--sign proc with certificate
ADD SIGNATURE TO dbo.create_login BY CERTIFICATE SecurityAdministratorCertificate;
GO
EDIT:
The above example encrypts the certificate private key using the database master key rather than a certificate-specific password. In a scenario where the database is restored or attached to a different SQL instance (without also restoring the master database), you'll need to recreate server level objects in the master database, including all logins needed by the application and certificates stored in master. To recover the certificate, one method is to copy the cert from the user database to master after the restore/attach and then recreate the certificate login with permissions. The DMK cannot be opened automatically in this case because the service master key, which encrypts the database master key, is different on the new instance. The original password is needed to open the DMK manually in the script that copies the cert into the master database. The certificate password used to copy the cert between databases is temporary and need not be retained.
Here's and example to recreate the cert and cert login in master after a restore or attach:
USE MyDatabase;
--open DMK with original password
OPEN MASTER KEY DECRYPTION BY PASSWORD='M@sterkEEPassw0rd';
--export cert from user database
USE MyDatabase;
DECLARE @CERTENC VARBINARY(MAX);
DECLARE @CERTPVK VARBINARY(MAX);
SELECT @CERTENC = CERTENCODED(CERT_ID(N'SecurityAdministratorCertificate'));
SELECT @CERTPVK = CERTPRIVATEKEY(CERT_ID(N'SecurityAdministratorCertificate'),
'temporary password here');
DECLARE @sql nvarchar(MAX);
SELECT @sql = N'CREATE CERTIFICATE SecurityAdministratorCertificate FROM BINARY = '
+ CONVERT(nvarchar(MAX), @CERTENC, 1)
+ ' WITH PRIVATE KEY ( BINARY = '
+ CONVERT(nvarchar(MAX), @CERTPVK, 1)
+ ', DECRYPTION BY PASSWORD = ''temporary password here'');'
SELECT @sql
CLOSE MASTER KEY;
--import cert into master
USE master;
--create database master key in new instance master database, if necessary
IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD='M@sterkEEPassw0rd';
END;
EXEC(@sql);
GO
--recreate login and assign permissions
CREATE LOGIN SecurityAdministratorCertificateLogin
FROM CERTIFICATE SecurityAdministratorCertificate;
GRANT ALTER ANY LOGIN TO SecurityAdministratorCertificateLogin;
GO