Script to find orpahend users

Posted: April 1, 2008 in Scripts to use

exec sp_change_users_login ‘Report’

———————————————————————————————-
Run Below script at source server

———————————————————————————————-
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 =’

, sid

FROM syslogins

WHERE name IN (‘username’)

———————————————————————————————-
Get the Output and execute on Destination server
———————————————————————————————-

EXEC sp_addlogin @loginame = ‘CorpCommUser’    , @defdb = ‘CorpComm’    , @deflanguage = ‘us_english’    , @encryptopt = ‘skip_encryption’    , @passwd =    0x01003F04413C64CEE4767BA2DD0053A02C6056640C4C88C24DFA    , @sid =    0xCEE1766A76520E43A98DCB141B031F7E

To get the report execute this query

———————————————————————————————-
Run This Script on given database

exec sp_change_users_login ‘Report’

———————————————————————————————-
If found USer then Run below Script

exec sp_change_users_login ‘auto_fix’, ‘OCAD_User’, null, ‘OCAD_User’

———————————————————————————————-

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