I created a Stored Procedure that performs the Following tasks:
- Creates database
- Change owner to sa
- Set recovery model
- Creates users in database
- Adds users to database roles.
- Grants execute and view definition to users
I then created a certificate, Login from cert, user from cert, granted exec on SP to user, granted control server to login, added signature to sp by certificate.
When non sysadmins execute the SP everything works except for adding users to the database roles with error: Cannot alter the role ‘db_datareader’, because it does not exist or you do not have permission.
If I add the login to the sysadmin server role, when non sysadmins execute the SP it will work. Ideally the login would have minimal permissions to perform these functions. Is it possible to do this w/o granting sysadmin to the user?
Here is the code I used to create the certificate, login, user, signature, etc:
use master;
go
CREATE CERTIFICATE sp_CreateNewReportingDatabase_cert
ENCRYPTION BY PASSWORD = 'SuperSecretPassword'
WITH SUBJECT = 'Certificate for sp_CreateNewReportingDatabase',
START_DATE = '20201015', EXPIRY_DATE = '21000101'
GO
CREATE LOGIN sp_CreateNewReportingDatabase_cert_user FROM CERTIFICATE sp_CreateNewReportingDatabase_cert;
GO
CREATE USER sp_CreateNewReportingDatabase_cert_user FROM CERTIFICATE sp_CreateNewReportingDatabase_cert;
GO
GRANT EXECUTE ON dbo.CreateNewReportingDatabase TO sp_CreateNewReportingDatabase_cert_user;
GO
GRANT CONTROL SERVER TO sp_CreateNewReportingDatabase_cert_user;
GO
ADD SIGNATURE TO (dbo).(CreateNewReportingDatabase) BY CERTIFICATE sp_CreateNewReportingDatabase_cert
WITH PASSWORD = 'SuperSecretPassword';
GO
Here is the code to create the stored proc:
CREATE OR ALTER PROC dbo.CreateNewReportingDatabase @DatabaseName NVARCHAR(50), @ReportingPod NVARCHAR(50) = NULL
AS
SET XACT_ABORT, NOCOUNT ON
DECLARE @SqlCommand NVARCHAR(2000);
--Error Checking
IF @ReportingPod NOT LIKE 'TAC(_)NTAnalysis-%' AND (@DatabaseName LIKE 'Reporting(_)%' OR @DatabaseName LIKE '(_)Salesforce')
BEGIN
RAISERROR('For Reporting_<client> and <client>_Salesforce databases you must populate @ReportingPod with a value that follow formate TAC_NTAnalysis-<reportingpod> Contact DBA',16,1)
SET NOEXEC ON --Stops running the script --I believe that this requires sysadmin rights.
END
ELSE IF (@DatabaseName LIKE 'Reporting(_)%' OR @DatabaseName LIKE '(_)Salesforce') AND @ReportingPod IS NULL
BEGIN
RAISERROR('For Reporting_<client> and <client>_Salesforce databases you must populate @ReportingPod with a value that follow formate TAC_NTAnalysis-<reportingpod> Contact DBA',16,1)
SET NOEXEC ON --Stops running the script --I believe that this requires sysadmin rights.
END
ELSE IF EXISTS (
SELECT (name)
FROM sys.databases
WHERE (name) = @DatabaseName
)
BEGIN
RAISERROR('There is already a detabase with that name!',16,1)
SET NOEXEC ON --Stops running the script --I believe that this requires sysadmin rights.
END
--Execute Scrips
ELSE
BEGIN
--Create the database
PRINT 'Creating database ' + @databasename;
SET @SqlCommand = N'CREATE DATABASE (' + @DatabaseName + ')'
EXEC dbo.sp_executesql @SqlCommand
-- change owner to sa
PRINT 'Switching owner to sa';
SET @SqlCommand = N'ALTER AUTHORIZATION ON DATABASE::(' + @DatabaseName + ') TO (sa);'
EXEC dbo.sp_executesql @SqlCommand;
-- set recovery model to simple
IF @DatabaseName LIKE 'Reporting(_)%' OR @DatabaseName LIKE '%(_)Salesforce'
BEGIN
PRINT 'Setting recovery model to simple';
SET @SqlCommand = N'ALTER DATABASE (' + @DatabaseName + ') SET RECOVERY SIMPLE;'
EXEC dbo.sp_executesql @SqlCommand
END
ELSE PRINT 'Revoery model not modified from default';
-- Create users for ServiceAccount and reportingPod in Reporting_ databases.
IF @DatabaseName LIKE 'reporting(_)%' OR @DatabaseName LIKE '%(_)Salesforce'
BEGIN
SET @SqlCommand = N'
USE (' + @DatabaseName + ')
IF NOT EXISTS(
SELECT (name)
FROM sys.database_principals
WHERE
(type) NOT IN (''s'',''r'')
AND (NAME) = ''TAC_NTServiceAccount''
)
BEGIN
PRINT ''Creating (TAC_NTServiceAccount) user''
CREATE USER (TAC_NTServiceAccount) FOR LOGIN (TAC_NTServiceAccount)
END
IF NOT EXISTS(
SELECT (name)
FROM sys.database_principals
WHERE
(type) NOT IN (''s'',''r'')
AND (NAME) = ''' + @ReportingPod + '''
)
BEGIN
PRINT ''Creating ' + @ReportingPod + ' user''
CREATE USER (' + @ReportingPod + ') FOR LOGIN (' + @ReportingPod + ');
END'
EXEC sp_executesql @SqlCommand;
END
ELSE PRINT 'Skipping the creation of the ServiceAccount and Reporting POD database principals'
---Grant role membership for ServiceAccount
IF @DatabaseName LIKE 'Reporting(_)%'
BEGIN
PRINT 'Adding !gampelan to roles: reader, writer, ddladmin';
SET @SqlCommand = N'
USE (' + @DatabaseName + ')
ALTER ROLE db_datareader ADD MEMBER (TAC_NTServiceAccount)
ALTER ROLE db_datawriter ADD MEMBER (TAC_NTServiceAccount)
ALTER ROLE db_ddladmin ADD MEMBER (TAC_NTServiceAccount)
'
EXEC sp_executesql @SqlCommand
END
ELSE IF @DatabaseName LIKE '%(_)salesforce'
BEGIN
PRINT 'Adding ServiceAccount to roles: reader';
SET @SqlCommand = N'
USE (' + @DatabaseName + ')
ALTER ROLE db_datareader ADD MEMBER (TAC_NTServiceAccount)
'
EXEC sp_executesql @SqlCommand
END
--Grant role membership for ReportingPod
IF @DatabaseName LIKE 'Reporting(_)%' AND @ReportingPod LIKE 'TAC_NTAnalysis%'
BEGIN
PRINT 'Adding ' + @ReportingPod + ' to roles: reader';
SET @SqlCommand = N'
USE (' + @DatabaseName + ')
ALTER ROLE db_datareader ADD MEMBER (' + @ReportingPod + ')'
EXEC sp_executesql @SqlCommand
END
ELSE IF @DatabaseName LIKE '%salesforce' AND @ReportingPod LIKE 'TAC_NTAnalysis%'
BEGIN
PRINT 'Adding ' + @ReportingPod + ' to roles: reader';
SET @SqlCommand = N'
USE (' + @DatabaseName + ')
ALTER ROLE db_datareader ADD MEMBER (' + @ReportingPod + ')'
EXEC sp_executesql @SqlCommand
END
--Grant execute and view definition for ReportingPod
IF @DatabaseName LIKE '%(_)Salesforce' OR @DatabaseName LIKE 'Reporting(_)%'
BEGIN
PRINT 'Granting execute and view definition to ' + @ReportingPod;
SET @SqlCommand = N'
USE (' + @DatabaseName + ')
GRANT EXECUTE TO (' + @ReportingPod + ');
GRANT VIEW DEFINITION TO (' + @ReportingPod + ');'
EXEC dbo.sp_executesql @SqlCommand;
END
END
GO