Script to find orpahend users

Posted: January 4, 2008 in Uncategorized

—–For Sql users—-Run the below query on Source Server’s..

Use Master
SET NOCOUNT ON
SELECT ‘EXEC sp_addlogin @loginame = ”’ + loginname + ””
,’, @defdb = ”’ + dbname + ””
,’, @deflanguage = ”’ + language + ””
,’, @encryptopt = ”skip_encryption”’
,’, @passwd =’
, cast(password AS varbinary(256))
,’, @sid =’
, sid
FROM syslogins where loginname Not like ‘NA%’ and loginname not like ‘Builtin%’ and loginname Not like ‘sa’

— For Windows Users—-
Use Master

SELECT ‘EXEC sp_grantlogin @loginame = ”’ + loginname + ””
,’ EXEC sp_defaultdb @loginame = ”’ + loginname + ””
,’, @defdb = ”’ + dbname + ””
FROM syslogins
WHERE loginname NOT IN (‘BUILTIN\Administrators’)
AND isntname = 1

—Copy the Results and Execute on Destination server’s where you have copied or migrated the databases

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s