Code Generation, T-SQL Style

Making your T-SQL work smarter for you by having it work as a code generator. Part 1 in a series.

Want to save some time writing code? Many DBAs use the trick I'm going to show you this week to: A) build scripts faster, and B) run ad-hoc queries faster. Essentially, you use SELECT statements as well as the T-SQL functions PRINT and EXEC() to build and display/execute T-SQL commands.

Check out this script:

SELECT    DISTINCT – Make sure we don't get duplicates
    'EXEC ' + db_name()
    + '.dbo.sp_change_users_login ''Update_One'', '
    + '''' + U.name + ''', '
    + '''' + U.name + '''' AS DeOrphanizeStatement
FROM    sys.database_principals AS U
    LEFT OUTER JOIN sys.sql_logins AS L
        ON L.[name] = U.[name] COLLATE SQL_Latin1_General_CP1_CI_AS --Ensures no Case problems
where    (U.type = 'S') --SQL Logins Only; no Windows Logins, no Roles
    AND U.name NOT IN ('dbo')
    AND U.sid IS NOT NULL --Eliminates INFORMATION_SCHEMA and PUBLIC
    AND U.name NOT IN ('guest') --Eliminates specific unwanted logins

Walking through the script, we can see that we're simply using a SELECT with a combination of explicit string values concatenated with values we are retrieving from various system tables. In this case, we're building commands that will help us execute the system stored procedure sp_change_users_login to restore the SIDs of SQL logins in a database. You might find you'll need to do this when restoring a copy of a database from one server to another. Fig. 1 shows sample output from this script.

DeOrphanize
Figure 1. Results from running the script to snag orphaned users.

We could now use this script in multiple databases, or even use the system stored procedure sp_msForEachdb to iterate through all of our databases and build a script to execute these commands as needed.

Now, let's say we want to take this a step further, and have the script actually generate AND execute the resultant T-SQL WITHOUT sp_msForEachdb. In this case, sp_change_users_login can run against logins that are OK without doing any damage, so we could run through all of our databases and make sure there are no orphaned users.

Next time, I'll cover using variables and the PRINT and EXEC() functions to do this even faster. Until then, have fun!

About the Author

Joshua Jones is co-author of A Developer's Guide to Data Modeling for SQL Server: Covering SQL Server 2005 and 2008 (Addison-Wesley Professional, 2008) and is a principal with Denver-based Consortio Services LLC.

Featured

  • SameSite Cookie Changes Rolled Back Until Summer

    The Chromium Project announced on Friday that it's delaying enforcement of SameSite cookie changes, and is temporarily rolling back those changes, because of the COVID-19 turmoil.

  • Basic Authentication Extended to 2H 2021 for Exchange Online Users

    Microsoft is now planning to disable Basic Authentication use with its Exchange Online service sometime in the "second half of 2021," according to a Friday announcement.

  • Microsoft Offers Endpoint Configuration Manager Advice for Keeping Remote Clients Patched

    Microsoft this week offered advice for organizations using Microsoft Endpoint Configuration Manager with remote Windows systems that need to get patched, and it also announced Update 2002.

  • Azure Edge Zones Hit Preview

    Azure Edge Zones, a new edge computing technology from Microsoft designed to enable new scenarios for developers and partners, emerged as a preview release this week.

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.