Several times over the last few weeks I’ve gotten a request to create a new work database. The individuals from this team each have their own database that they can use as a type of scratch pad and I guess they’ve been hiring. It’s simple enough to create the database and then grant the necessary permissions, but let’s face it, after the first time I was already tired of the GUI and scripted the process out. Running the script was better but I quickly became annoyed at having to make changes to the script. User names etc. So I decided to create a more permanent script. My first thought was to use dynamic SQL. While that would work, and I’m certainly comfortable with dynamic SQL, it just didn’t feel right for this. I decided in the end to use a trick from templates. If you set up parameter(s) in the file you can use Ctrl-Shift-M to scroll through them and make changes. In an odd twist these are called template parameters. As an example here is a simplified version of the script I wrote:
-- Ctrl-Shift-M to update parameters
CREATE DATABASE ScratchPad_<user_abbreviation, char(3), ABC>;
GO
USE ScratchPad_<user_abbreviation, char(3), ABC>;
GO
IF DATABASE_PRINCIPAL_ID('db_execute_all_dbo') IS NULL CREATE ROLE [db_execute_all_dbo] AUTHORIZATION [dbo];
IF DATABASE_PRINCIPAL_ID('<user_name, sysname, domainabcdef>') IS NULL CREATE USER [<user_name, sysname, domainabcdef>] FOR LOGIN [<user_name, sysname, USabcdef>] WITH DEFAULT_SCHEMA = [dbo];
EXEC sp_addrolemember @rolename = 'db_execute_all_dbo', @membername = '<user_name, sysname, domainabcdef>';
EXEC sp_addrolemember @rolename = 'db_ddladmin', @membername = '<user_name, sysname, domainabcdef>';
EXEC sp_addrolemember @rolename = 'db_datareader', @membername = '<user_name, sysname, domainabcdef>';
EXEC sp_addrolemember @rolename = 'db_datawriter', @membername = '<user_name, sysname, domainabcdef>';
GRANT EXECUTE ON SCHEMA::[dbo] TO [db_execute_all_dbo] AS [dbo];
GRANT VIEW DEFINITION TO [<user_name, sysname, domainabcdef>] AS [dbo];
You’ll notice I have a comment at the top to remind me of the shortcut to fill in the template parameters. It’s not one I use often so I need the help. When I use the shortcut it brings up the two parameters. The green in the background is where the parameters are that are about to be filled in.
Each parameter has a name, a data type (that seems to be for display only), and a default value. If you look back at the code you’ll see that they are set up like this:
<name, data type, default value>
Fill in each of the values. You can use TAB or UP-Arrow | DOWN-Arrow to flip between them. Once you are happy with what you have, and it’s perfectly reasonable to just leave the defaults if you’ve set them up correctly, hit OK to actually make the replacement.
-- Ctrl-Shift-M to update parameters
CREATE DATABASE ScratchPad_ABC;
GO
USE ScratchPad_ABC;
GO
IF DATABASE_PRINCIPAL_ID('db_execute_all_dbo') IS NULL CREATE ROLE [db_execute_all_dbo] AUTHORIZATION [dbo];
IF DATABASE_PRINCIPAL_ID('domainabcdef') IS NULL CREATE USER [domainabcdef] FOR LOGIN [domainabcdef] WITH DEFAULT_SCHEMA = [dbo];
EXEC sp_addrolemember @rolename = 'db_execute_all_dbo', @membername = 'domainabcdef';
EXEC sp_addrolemember @rolename = 'db_ddladmin', @membername = 'domainabcdef';
EXEC sp_addrolemember @rolename = 'db_datareader', @membername = 'domainabcdef';
EXEC sp_addrolemember @rolename = 'db_datawriter', @membername = 'domainabcdef';
GRANT EXECUTE ON SCHEMA::[dbo] TO [db_execute_all_dbo] AS [dbo];
GRANT VIEW DEFINITION TO [domainabcdef] AS [dbo];
At this point I now have a script that I can use over and over again to create this database and apply the permissions. My actual script is quite a bit more complicated and has values for database size, file locations, etc. I don’t need to change them often but with the default values I don’t really have to.