Use T-SQL, not the GUI

I preach about writing T-SQL for admin and dev tasks, instead of using the user interface in SQL Server Management Studio. Why? Because scripts are saveable, repeatable, and excellent for CYA* events.

Case in point: I had an extensive security request from a client yesterday. I frequently use Minion. Enterprise (of course) to script out and edit/apply permissions as necessary, but this time it was a brand new security set. So, I generated the necessary scripts.This was close to what I did:

SELECT ‘USE [‘ + name + ‘]; CREATE USER [domain\user1] FROM LOGIN [domain\user1]; EXEC sp_addrolemember ”db_datareader”, ”domain\user1”;’ FROM sys.databases WHERE …

This query generated the scripts that I wanted, and I was able to copy-paste and run. Easy!

But it turns out, dear reader, I missed something. My WHERE clause wasn’t complete, and I neglected a couple of databases that the user needed to get into.

When user1 wrote to ask why they couldn’t get into database X, I was able to go back, look at the script, and see my omission.

This is a simple example, of course, but I cannot tell you how many times it’s been useful to look at scripts ran on previous dates, either to diagnose an issue, or to prove that “yes, we did perform actions A and B”.

Bonus tip: I tend to name my files by date and task. So for example, I might save the script above (and the generated script, and the name of the server I ran it on) as “20190822_Server3 user1 readonly perms.sql”. This makes files easily searchable by date, server, and task.

Cheers,

Jen

*Cover Your Butt

3 thoughts on “Use T-SQL, not the GUI”

  1. Greg Moore

    I’ll admit to using the GUI a lot… but rarely hit the OK button. I’ll hit the generate script button and use that script and save that.

    I find it’s the best of both worlds in some cases, the advantages of point and click of the, with all the advantages of an actual script like you mention.

    But yeah, ultimately, I think scripting wins hands down.

    1. Jen McCown Post author

      I definitely do this for some of the more fiddle stuff. But it totally still counts!

      I also use a fair amount of generated TSQL, like the example in the article.

  2. Pingback: Dew Drop – August 26, 2019 (#3017) | Morning Dew

Comments are closed.