Script out logins and users

Question

Script out logins and users

StAinley

SSC Veteran

Points: 280
More actions
December 23, 2003 at 10:41 am

#160318

I believe it's possible to script server logins and database users using DMO but I am failing miserably. I need to do this as part of a process that scripts out logins/users prior to copying a production database into live and then scripts the test rather than live logins/users back into database dropping the live logins/users.
If it's do-able, any pointers would be gratefully accepted. Or other suggestions as to how I might go about it. The important part is that it needs to be intervention free and run in DTS or just a job.
Merry Christmas.
Steven

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

jxflagg Hall of Fame Points: 3817 More actions · Answer 1

From Enterprise Manager, click on Data Transformation Services and then Local Packages, then right click to create a new package.

Under Tasks, you'll see that there is a type of task to transfer logins.

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 2

Here is an article I wrote that might give you another method of scripting login migrations:

http://www.sqlservercentral.com/columnists/glarsen/migratingloginstoanotherserver.asp

Gregory A. Larsen, DBA

Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http://www.sqlservercentral.com/bestof/

Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Jonathan SSC-Insane Points: 20427 More actions · Answer 3



Sub logins(Server As String, DB As String)

Dim objServer As New SQLDMO.SQLServer

Dim objDatabase As New SQLDMO.Database

Dim objLogin As SQLDMO.Login

Dim objUser As SQLDMO.User

objServer.LoginSecure = True

objServer.Connect Server

Set objDatabase = objServer.Databases(DB)

 For Each objLogin In objServer.logins

  Debug.Print objLogin.Script(SQLDMOScript_Drops)

 Next

 For Each objUser In objDatabase.Users

  Debug.Print objUser.Script(SQLDMOScript_Drops)

 Next

End Sub

--Jonathan

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 4

why not

select * from syslogins (from the master database)

select * from sysusers (from each user database)

Bye

Gabor

Bye
Gabor

jxflagg Hall of Fame Points: 3817 More actions · Answer 5

quote:
why not
select * from syslogins

syslogins doesn't contain the password. I believe you could do something like the following, but I wouldn't mess with it as long as there are easier ways.

On source server:

DECLARE @binpwd sysname,

@sidid binary(16)

select @binpwd = a.password,

@sidid = a.sid

from sysxlogins a,

syslogins b

where a.sid = b.sid

and b.name = '[name]'

On destination server:

EXEC sp_addlogin '[name]',

@passwd= @binpwd,

@defdb = 'Master',

@deflanguage = 'us_english',

@sid = @binsid,

@encryptopt = 'skip_encryption'