Synchronizing Logins

Congratulations to Michael S. Armentrout, senior DBA at Dallas-based SWS Securities, and Werner Geuens, consultant MCDBA at Cronos in Kontich, Belgium. Michael won first prize of $100 for the best solution to the August Reader Challenge, "Synchronizing Logins". Werner won second prize of $50. Honorable mention goes to Peter Lin, who was the first-place winner in our May Reader Challenge. Here’s a recap of the problem and the solution to the August Reader Challenge.

Problem

Ray’s company runs SQL Server 7.0 on its production servers and SQL Server 2000 on its staging servers. Ray needs to build a script that can synchronize logins between the production and staging servers (i.e., add missing logins from the production servers to the staging servers). Synchronized logins will let him create an identical environment for testing application upgrades, SQL Server, and the operating system on a different server. When a staging server is configured identically to a production server and holds the same data, he can also test service-pack upgrades on the staging server. Then, after the upgrade is finished, he can switch the server roles.

The production servers are configured for mixed authentication, which means that users can connect to a SQL Server instance by using either Windows authentication or SQL Server authentication. Help Ray write a script that can synchronize the logins between the servers while preserving all login properties.

Solution

Ray can choose between two efficient solutions to his problem: using SQL Distributed Management Objects (SQL-DMO) to script the login information or querying the syslogins table in the master database. When he uses SQL-DMO, Ray can enumerate the logins on the production server by using the Logins collection. Then, by using the Script method of the Login object, he can obtain the stored-procedure calls that create each login and its details: name, encrypted password, default database and language, and SID. The following VBScript code generates a file that contains the stored-procedure calls:

Dim oSQLServerDim oDatabaseDim oLoginDim ScriptOptions, ScriptOptions2Set oServer = CreateObject("SQLDmo.SqlServer")oServer.LoginSecure = TrueoServer.Connect "prod01"SQLDMOScript_AppendToFile=256SQLDMOScript_ToFileOnly=64SQLDMOScript_PrimaryObject=4SQLDMOScript2_LoginSID=1048576SQLDMOScript2_EncryptPWD=128SQLDMOScript2_UnicodeFile=4ScriptOptions = SQLDMOScript_AppendToFile Or _                         SQLDMOScript_ToFileOnly Or _                         SQLDMOScript_PrimaryObjectScriptOptions2 = SQLDMOScript2_LoginSID Or _                            SQLDMOScript2_EncryptPWD Or _                            SQLDMOScript2_UnicodeFile

The SQL-DMO solution provides a way of writing code that requires minimal changes to work with both SQL Server 2000 and 7.0.

Alternatively, Ray can query the syslogins system table in the master database to retrieve the details about each login on the production (SQL Server 7.0) servers. He can also use syslogins to determine whether a login is a Windows NT login by looking at the isntuser column value. He needs to know this information because the procedure to grant access to a SQL Server standard login is different from that of an NT login. This column returns 1 for NT logins and groups. The following query returns the login details and the NT login status:

SELECT l.loginname, l.password, l.dbname, l.language, l.sid,               l.isntuser      FROM master.dbo.syslogins AS l      WHERE l.loginname IS NOT NULL

Next, Ray can use this information to create identical logins on any staging (SQL Server 2000) server. To determine whether the login exists on a staging server, he can use the IS_SRVROLEMEMBER() system function, which returns NULL for logins that don’t exist on the server. (If the login exists, he can skip the creation procedure. Note that this procedure only adds the missing logins. It doesn’t synchronize a change in passwords because SQL Server doesn’t support a way to update the password directly other than at creation time.)

To add the standard logins to a staging server, Ray can use the sp_addlogin system stored procedure along with the login name, password, default database and language, and SID parameters. In addition to these parameters, he needs to use the skip encryption option to prevent the password from being encrypted again. (If the password is encrypted again, the user won’t be able to log in by using the original password.) The following call adds the standard SQL Server login:

EXEC sp_addlogin @name, @password, @dbname, @language,               @sid, 'skip_encryption'

If the logins he needs to add to the server are NT logins and groups, Ray can use the sp_grantlogin system stored procedure along with the login name parameter. He can set the logins’ default database and language by calling the sp_defaultdb and sp_defaultlanguage system stored procedure, respectively. The following calls add the NT login and all the login settings:

EXEC sp_grantlogin @nameEXEC sp_defaultdb @name, @dbnameEXEC sp_defaultlanguage @name, @language

Now Ray can incorporate these stored-procedure calls in a script of his choice. The following sample script uses a distributed query to add the logins from the prod01production server to a staging server:

DECLARE @logins cursorDECLARE @name sysname, @password sysname,                        @dbname sysname, @language sysname,                        @sid binary(16), @isntuser bitSET @logins = cursor fast_forward FOR      SELECT l.loginname, l.password, l.dbname, l.language, l.sid,                     l.isntuser       FROM prod01.master.dbo.syslogins AS l         WHERE l.loginname IS NOT NULLOPEN @loginsWHILE(1=1)BEGIN      FETCH @logins INTO @name, @password, @dbname,                    @language, @sid, @isntuser      IF @@fetch_status 

Now, test your SQL Server savvy in the September Reader Challenge, "Creating Indexed Views" (below). Submit your solution in an email message to [email protected] by August 14. SQL Server MVP Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Here’s the challenge: As the database architect for several SQL Server 2000 databases, Nick faces performance problems with several ad hoc queries that perform complex joins and aggregations. He decides that creating a few indexed views will generate significant performance gains without requiring him to modify most of the existing code. This solution should be effective because the query optimizer can rewrite the queries dynamically to use the indexed views rather than querying the larger base tables. However, after he creates the indexed views, he realizes that they change the way the code interacts with the base tables and performs the ad hoc queries.

Failure of the stored procedures that modify the base tables in the indexed views is Nick’s first hint that creating the indexed views has changed the code. (The applications that invoke the stored procedures and modify the base tables connect to SQL Server by using the ODBC, OLE DB, or ADO APIs.) When Nick tries to invoke some of the code, he receives the following sample error message:

Server: Msg 1934, Level 16, State 1, Line 1INSERT failed because the following SEToptions have incorrect settings:'QUOTED_IDENTIFIER,ARITHABORT'.

Help Nick take the necessary steps to ensure that the stored procedures and any code he uses on the base tables work properly after he creates the indexed views.