Recently I received a security request and realized I wasn’t comfortable in my ability to script out the t-sql commands I needed. By “comfortable” I mean I couldn’t create a login, then create the user, then add that user to a couple of roles and grant it permissions to some specific tables and stored procedures. All without looking at BOL. Now, that is my comfort level and I certainly wouldn’t push it on anyone else. It can seem a bit excessive to others, or maybe not. And I’ll be honest I don’t stress out if I forget an option or two, but I really feel like I should know how to script the basics of most, if not all of the tasks that I run across. Then if for some reason I can’t use SSMS, for example when using the DAC (dedicated admin connection) I’m still functional. Or sometimes I find that I’m going to repeating the same thing over and over, say creating multiple users, and I can just script it and do some copying and pasting. Frequently I find that after some practice I can script out even a single command faster than I can use the GUI.

Now I could go look at BOL and memorize the commands but that isn’t really my favorite way to learn stuff like this. One of the things I really like about SSMS is the ability to script out almost every action. In this particular case I used the SSMS GUI just like I normally would. Then I hit the script button.

Now I have a script that does what I need it to. I read it, try to make sure I understand all the options listed, do my best to remember it for next time, but don’t stress too much about it. If I have to do several repeated actions, say create more than one login, add the user to more than one role etc, I only use the GUI to script out one of them, then type the rest using the first as a model.

Now, the next time I get a similar request I do my best to remember the command. I might write up the script myself, then go through the same process using the SSMS GUI to script it out again. Then I compare the two scripts. Hopefully I’ve gotten it right, but if not, I make any needed corrections to my script. I’ll go through this the next few times I need a given script. Over time I get more and more familiar with the script and with its options and I have to reference back to SSMS or BOL less and less.

Now, please understand, I do go back and read BOL from time to time. You will never learn everything about a command by scripting it. There is a lot of information in BOL, and books and blogs for that matter, that you really should know, that you will never learn using this method. For example the permissions required and information on WHY to use a command. However, I have found this is a good way to learn on the go, while getting my job done.