Script users

What is the best way to script user logins after a databases restore from another server
all users came over but no logins
currently using this:

exec sp_addrolemember N'db_owner', N'Tman'

I have many users.  Is there a way to script logins for all the users
 
ohemaaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rboyd56Commented:
In Enterprise Manager
Select the origina database
Right click and choose all tasks
Generate SQL Script
On the options tab select script users and logins options.
0
dqmqCommented:
Kinda difficult to automate because one user (which exists at the database level) can have many logins (which exist at the server level).  Then, possibly, some of those logins have nothing to do with the database you just restored or different security implications on the other server.  

Probably the farthest I would go is to generate the create logins to a script file (don't know about 2000, but on 2005 you can do that on the database, tasks menu in SSMS) and then audit it manually.

Have you considered using Windows Authentication to circumvent this nonsense?
0
ohemaaAuthor Commented:
rboyd56:
Are you referring to these 2 options
1.Script database users and database roles
2.Script Sqlserver Logins(windows and server Logins)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

rboyd56Commented:
Even if you use Windows authentication there has to be a SQL Server login. The NT user (or group)  is just mapped to a SQL Server login.
0
ohemaaAuthor Commented:
so are these the 2 scripts that I need
0
rboyd56Commented:
No it will create one that has both
0
rboyd56Commented:
That should have been:

No it will create one that has both logins and users
0
ohemaaAuthor Commented:
did get some logins but not all
0
rboyd56Commented:
It may get only the logisn that are mapped to uses in that particular database. So you may need to do this for all databases to get all the logins.
0
ohemaaAuthor Commented:
ok I think restoring the system Db's (master, model, msdb) is easier.  it brings all the users and logins to the new server
0
rboyd56Commented:
The system database will only bring the logins, not the users of the user databases. Also if you resore teh master, the paths to the databases have to match or the database will come up suspect. Also if you have SQL Server authenticated logins (not NT authenticated logins) the SIDe probably will not match and you have to run sp_change_users_login in each database to sync them.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ohemaaAuthor Commented:
I restored system db's to the same path as old.  The users databases came as suspects.  I restored them from backups and they are all ok.  I have all users and logins.  Am i good?  I cannot test it now becasue the old one is still in production.  downtime will be next week.  this is just practise.  Am I going to be ok if I do the same during actual downtime.  
0
rboyd56Commented:
If all the databases came up tehn I think you should be ok. You may want to restore the msdb database as well. It holds any scheduled jobs and DTS packages.
0
ohemaaAuthor Commented:
Yes.  I restored all the user databases from recent backup ok. I restored MSDB as well.  it brought all the jobs and DTS packages over so I think I am good.  what do you think?
0
rboyd56Commented:
I would think so
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.