ronmichael
6/6/2013 - 6:38 PM

Script out all your MSSQL users - with passwords and group memberships - so you can recreate them on another server. Credits to http://weblo

Script out all your MSSQL users - with passwords and group memberships - so you can recreate them on another server. Credits to http://weblogs.sqlteam.com/billg/archive/2010/07/08/Scripting-out-SQL-Server-Logins.aspx for the original version; this variation does not require you to create a new function (fn_hexadecimal); it uses the fn_varbintohexstr function already in MSSQL.

script-users.sql
content_copyfile_download
select 
'IF NOT EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''')
    CREATE LOGIN [' + [name] + '] 
        WITH PASSWORD=' + master.sys.fn_varbintohexstr(password_hash) + ' HASHED,
        SID = ' + master.sys.fn_varbintohexstr([sid]) + ',  
        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'
from master.sys.sql_logins 
where type_desc = 'SQL_LOGIN' 
and [name] not in ('sa', 'guest');


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%';