LISTING 2: Code That Generates sp_grantlogin
 Lines for Domain Accounts

USE MASTER
GO
SET NOCOUNT ON
-- This query generates sp_grantlogin lines 
for domain accounts that aren’t on the local server.
SELECT 'EXEC sp_grantlogin @loginame = ''' + a.loginname + ''''
,' EXEC sp_defaultdb @loginame = ''' + a.loginname + '''' +
', @defdb = ''' + a.dbname + ''''
FROM
(SELECT substring(s.loginname,1,charindex('\',s.loginname)-1)
server,s.loginname,s.dbname FROM syslogins s
WHERE s.loginname NOT IN ('BUILTIN\Administrators') AND isntname = 1) a
WHERE server <> @@servername

-- This query generates sp_grantlogin lines for 
an account that’s on the local server. You need to
-- use a tool such as ADDUSERS.EXE (from the Microsoft 
Windows NT Resource Kit) to
-- synchronize the local SAM for the server before
 you add the logins.
PRINT 'DECLARE @cmd1 varchar(255), @cmd2 varchar(255)'
SELECT 'SELECT @cmd1 = ''EXEC sp_grantlogin @loginame = '''''' +
@@servername + ''\' +
a.loginname + '''''''' + char(13)
+ 'SELECT @cmd2 = ''EXEC sp_defaultdb @loginame = '''''' + @@servername +
''\'
+ a.loginname + '''''' + ', @defdb = ''''' + a.dbname + '''''''' +
char(13) + 'EXEC (@cmd1)' + char(13) + 'EXEC (@cmd2)' + char(13) + ' '
FROM
(SELECT substring(s.loginname,1,charindex('\',s.loginname)-1) server,
substring(s.loginname,charindex('\',s.loginname)+1,128) loginname,s.dbname
FROM syslogins s
WHERE s.loginname NOT IN ('BUILTIN\Administrators') AND isntname = 1) a
WHERE server = @@servername