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

  • Microsoft Offers More Help on Windows Server 2008 Upgrades

    Microsoft this week published additional help resources for organizations stuck on Windows Server 2008, which fell out of support on Jan. 14.

  • Microsoft Ups Its Carbon Reduction Goals

    Microsoft on Thursday announced a corporatewide carbon reduction effort that aims to make the company "carbon negative" by 2030.

  • How To Dynamically Lock Down an Unattended Windows 10 PC

    One of the biggest security risks in any organization happens when a user walks away from their PC without logging out. Microsoft has the solution (and it's not a password-protected screensaver).

  • First Stable Chromium-Based Microsoft Edge Browser Released

    Microsoft on Wednesday announced the first release of its Chromium-based Microsoft Edge browser at the "stable" commercial-release stage.

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.