I’m sure you’ve heard the story by Stephen Covey about the professor that fills a jar with rocks, pebbles, and sand as a metaphor for time management and prioritization. I’d relate it here, but I think this video is much more fun.
Obviously this metaphor applies to all aspects of our lives, but we can also take heed of its lesson when it comes to our job. Too often, we as DBAs are so busy handling little tasks like managing users, migrating databases from this server to that one, checking job statuses, etc., that we don’t have time to focus on bigger things, like baselining, inventorying, performance tuning, and designing disaster recovery plans. Little tasks (aka the pebbles) might not take up much time in and of themselves, but sometimes they interrupt a development process or train of thought, which also hinders productivity and creativity. I’m a big proponent of empowering developers and other IT support personnel to do certain tasks themselves. Within reason, of course. Letting them handle some of the little tasks themselves frees us up for the bigger stuff.
I have a certain application that gets requests to add or remove users fairly regularly. The original request is sent to the application support team, they forward the requests to the DBA team to create the login and database user. We notify the support team once that’s been done so that they can complete user setups within the application. I’ve already put the create and drop logic into stored procedures, so again, the actual act of creating the logins doesn’t take much time. But since it is already scripted, it’s the perfect candidate for delegating to the support team.
There were a few options for allowing non-sysadmin users the ability to run my procedures.
- Grant them admin rights directly
- Modify the script to run as an admin login, grant the support team impersonate rights to that admin login
- Sign the stored procedures with a certificate
Obviously granting admin rights directly to the support team’s logins is not the ideal approach, so I wasn’t even going to entertain that idea. I played with the idea of using EXECUTE AS in the stored procedure, but where I kept getting hung up was granting IMPERSONATE to the support team. While the risk was relatively low that someone would abuse that privilege, it was still a risk. The third option, however, was not only effective, it was also highly secure.
Signing a stored procedure with a certificate is actually a very simple process. The first thing I had to do was create the certificate itself. I’m creating mine in master, since that’s where my procedure is.
USE master; GO CREATE CERTIFICATE [MyAppCert] ENCRYPTION BY PASSWORD = 'BestPassw0rdEvar!' WITH SUBJECT = 'Certificate for signing MyApp stored procedures', START_DATE = '2011-12-01', EXPIRY_DATE = '2025-12-31'; GO
Once the certificate has been created, I can create a login from the certificate and make that login a member of the sysadmin server role.
CREATE LOGIN [MyAppUserCreator] FROM CERTIFICATE [MyAppCert]; EXEC sp_addsrvrolemember 'MyAppUserCreator', 'sysadmin' GO
According to the principal of least privilege, I should have granted ALTER ANY LOGIN to MyAppUserCreator and then granted ALTER ANY USER in the database, rather than granting sysadmin. And I considered doing that. But while it wouldn’t have been a problem in Production, in our QA and Dev environments, we sometimes have several copies of this application’s database and we’d (read: I’d) need to make sure the login had a user in all these databases with the appropriate rights. In the end I decided to keep it simple.
Ok, so the next step was to create the stored procedure, which I had already done. I’ve simplified it here.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[sp_create_myapp_user] (@new_user varchar(100), @passwd varchar(100)) as begin declare @db varchar(200), @sqlstmt nvarchar(4000), @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT; SET @db = 'MyDatabase' --create login BEGIN TRY print 'Creating login ' + @new_user + '...' set @sqlstmt = N'CREATE LOGIN ' + @new_user + ' WITH PASSWORD = ''' + @passwd + ''' , ' + 'DEFAULT_DATABASE = ' + @db + ';' exec sp_executesql @sqlstmt print '... login created!' print 'Creating user ' + @new_user + ' in database ' + @db + '...' set @sqlstmt = N'USE '+ @db +'; CREATE USER '+ @new_user + ' FOR LOGIN '+ @new_user + ';' exec sp_executesql @sqlstmt print '... user created!' print 'Adding user ' + @new_user + ' to MyAppReader role in database ' + @db + '...' set @sqlstmt = N'USE '+ @db +'; EXEC sp_addrolemember ''MyAppReader'', '''+ @new_user + ''';' exec sp_executesql @sqlstmt print '... user added to role!' END TRY BEGIN CATCH if error_number() = 15025 begin print '... login ' + @new_user + ' already exists.' end else if error_number() = 15023 begin print '... user '+@new_user+' already exists.' end else begin SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ); end END CATCH END GO
Notice it’s just an ordinary stored procedure. No fancy code, no EXECUTE AS, nothing. As it stands now, even if I granted a non-admin user execute permissions on the procedure, it wouldn’t work for them, since it would be running with their privileges. To make the procedure run with sysadmin privileges, we have to sign it with the certificate we created earlier.
ADD SIGNATURE TO OBJECT::[sp_create_myapp_user] BY CERTIFICATE [MyAppCert] WITH PASSWORD = 'BestPassw0rdEvar!'; GO
Now it’s just a matter of granting the support team users access to the procedure.
CREATE ROLE [MYAPP_SUPPORT_ROLE] AUTHORIZATION [dbo] GO grant execute on sp_create_myapp_user to MYAPP_SUPPORT_ROLE CREATE USER NonAdminUser FOR LOGIN NonAdminLogin; exec sp_addrolemember 'MYAPP_SUPPORT_ROLE', 'NonAdminUser';
And with that I’ve allowed non-admin users to perform a specific admin-level duty, without directly granting them any elevated privileges. I hope this gets you thinking about how you can get some of the pebbles out of your jar so you can focus on the bigger rocks.
PSA: Please don’t actually throw stones at your developers. No matter how much you think they deserve it.
Could you extend the example to demonstrate how to sign a stored procedure that is not stored in the master database, but is instead stored in a DBA utility database? I ask because the ‘CREATE LOGIN … FROM CERTIFICATE’ statement requires the certificate to reside in the master database. However, ‘ADD SIGNATURE …’ does not allow the certificate to reside in another database. I tried to work around the problem by creating the certificate a second time in the utility database but that resulted in a different certificate thumbprint so the permissions weren’t granted. Is the solution to export the certificate from master and then import it to the utility database so as to preserve the original certificate thumbprint?
Greg – I think the thing to do would be to create the certificate in the utility database, back it up to a file, use that file to create the certificate in the master database. Then create a login from the master certificate and create an associated user in the utility database, and grant execute on the stored proc to the user. Jonathan Kehayias goes through a demo here. His example deals with multiple user databases, but it should work for what you’re trying to do.