Database Scripts

Beta
 Log In    |   Sign Up

SQL Server : SQL Login move script with password

Added on Apr-17-2014 by Jelong
For SQL Server

Tags : login copylogin scriptingsecurity

This script generates SQL user login script with password. it's very useful to move (a) login(s) to other server or instance with same password. (It's not move permission or roles!)

Versions

SQL Server 2000, 2005, 2008, 2012

DOWNLOAD

USE master
GO
SET NOCOUNT ON
SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + ''''
+', @defdb = ''' + dbname + ''''
+', @deflanguage = ''' + language + ''''
+', @encryptopt = ''skip_encryption'''
+', @passwd =', CAST(password AS varbinary(256))
,', @sid =', CAST(sid as varbinary(256)) 
FROM syslogins
WHERE name NOT IN ('sa')
AND isntname = 0 AND password IS NOT NULL
ORDER BY loginname


SELECT 'EXEC sp_grantlogin @loginame = [' + loginname + ']'
+' EXEC sp_defaultdb @loginame = [' + loginname + ']'
+', @defdb = [' + dbname + ']'
FROM syslogins
WHERE loginname NOT IN ('BUILTIN\Administrators','NT AUTHORITY\SYSTEM')
AND isntname = 1
ORDER BY loginname

    

Report Script

blog comments powered by Disqus