posts - 220, comments - 411, trackbacks - 27

My Links

Advertisement

News

Follow billgraziano on Twitter

Article Categories

Archives

Post Categories

Consulting

SQL Server

Scripting out SQL Server Logins

I regularly move logins between servers.  Mostly this is between production and our DR site.  I’ve used the code in KB246133 many, many times but it’s pretty limited.  I started with that and wrote the script below.

  • There is a user-defined function that’s created in master.  You can create it in any database you want but you’ll need to update the script.  The function converts varbinary hashed passwords to a string representation.
  • It keeps the password intact for SQL Server logins.
  • It scripts both Windows logins and SQL Server logins.  It also scripts role membership.
  • It keeps the SID intact for SQL Server logins.  This is important so you don’t have to remap users to logins.
  • The script that is generated uses IF NOT EXISTS so that it doesn’t try to create logins that already exist.
  • It DOES NOT handle removal of logins from roles.  It does handle disabled accounts but I haven’t done much testing on that.
  • I’ve tested this on SQL Server 2005 and SQL Server 2008.
  • You’ll probably need to change your results so that you display more characters by default.  Under Tools –> Options –> Query Results –> SQL Server –> Results to Text increase the maximum number of characters returned to 8192 (or a number high enough that the results aren’t truncated).  You’ll want to set results to text before running this.
USE [master]
GO

/****** Object: UserDefinedFunction [dbo].[fn_hexadecimal] ****/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_hexadecimal]
(
-- Add the parameters for the function here
@binvalue varbinary(256)
)
RETURNS VARCHAR(256)
AS
BEGIN

DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
return @charvalue

END
GO


SET NOCOUNT ON
GO
--use MASTER
GO
PRINT '-----------------------------------------------------------------------------'
PRINT '-- Script created on ' + CAST(GETDATE() AS VARCHAR(100))
PRINT '-----------------------------------------------------------------------------'
PRINT ''
PRINT '-----------------------------------------------------------------------------'
PRINT '-- Create the windows logins'
PRINT '-----------------------------------------------------------------------------'
SELECT 'IF NOT EXISTS (SELECT * FROM master.sys.server_principals WHERE [name] = ''' + [name] + ''')
CREATE LOGIN ['
+ [name] + '] FROM WINDOWS WITH DEFAULT_DATABASE=[' +
default_database_name + '], DEFAULT_LANGUAGE=[us_english]
GO

'

FROM master.sys.server_principals
where type_desc In ('WINDOWS_GROUP', 'WINDOWS_LOGIN')
AND [name] not like 'BUILTIN%'
and [NAME] not like 'NT AUTHORITY%'
and [name] not like '%\SQLServer%'
GO

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Create the SQL Logins'
PRINT '-----------------------------------------------------------------------------'
select 'IF NOT EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''')
CREATE LOGIN ['
+ [name] + ']
WITH PASSWORD='
+ [master].[dbo].[fn_hexadecimal](password_hash) + ' HASHED,
SID = '
+ [master].[dbo].[fn_hexadecimal]([sid]) + ',
DEFAULT_DATABASE=['
+ default_database_name + '], DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION='
+ CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY=OFF
GO
IF EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = '
'' + [name] + ''')
ALTER LOGIN ['
+ [name] + ']
WITH CHECK_EXPIRATION='
+
CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY=' +
CASE WHEN is_policy_checked = 1 THEN 'ON' ELSE 'OFF' END + '
GO


'

--[name], [sid] , password_hash
from master.sys.sql_logins
where type_desc = 'SQL_LOGIN'
and [name] not in ('sa', 'guest')

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Disable any logins'
PRINT '-----------------------------------------------------------------------------'
SELECT 'ALTER LOGIN [' + [name] + '] DISABLE
GO
'

from master.sys.server_principals
where is_disabled = 1

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Assign groups'
PRINT '-----------------------------------------------------------------------------'
select
'EXEC master..sp_addsrvrolemember @loginame = N''' + l.name + ''', @rolename = N''' + r.name + '''
GO

'

from master.sys.server_role_members rm
join master.sys.server_principals r on r.principal_id = rm.role_principal_id
join master.sys.server_principals l on l.principal_id = rm.member_principal_id
where l.[name] not in ('sa')
AND l.[name] not like 'BUILTIN%'
and l.[NAME] not like 'NT AUTHORITY%'
and l.[name] not like '%\SQLServer%'



Print | posted on Thursday, July 08, 2010 11:09 AM | Filed Under [ SQL Server Stuff Utilities KCTechBlog Syndication ]

Feedback

Gravatar

# re: Scripting out SQL Server Logins

Here's one that I wrote a while back to sync our primary and DR sites: http://weblogs.sqlteam.com/tarad/archive/2008/06/24/How-to-transfer-SQL-logins-between-SQL-Server-2005-instances.aspx

We move production to our DR site twice a year, so I needed an easy way to do the work. My code doesn't print out the commands, but rather it syncs the two servers.

I should probably go back and incorporate some of your code into mine to make it more robust!
7/8/2010 11:49 AM | Tara
Gravatar

# re: Scripting out SQL Server Logins

Holy Smokes! You had what I wanted already written. GAH! :)
7/8/2010 4:22 PM | Bill Graziano
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET