Solved

Script users

Posted on 2007-03-19
15
426 Views
Last Modified: 2012-06-21
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
 
0
Comment
Question by:ohemaa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
15 Comments
 
LVL 16

Expert Comment

by:rboyd56
ID: 18749557
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
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 100 total points
ID: 18749622
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
 

Author Comment

by:ohemaa
ID: 18749685
rboyd56:
Are you referring to these 2 options
1.Script database users and database roles
2.Script Sqlserver Logins(windows and server Logins)
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 16

Expert Comment

by:rboyd56
ID: 18749695
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
 

Author Comment

by:ohemaa
ID: 18749723
so are these the 2 scripts that I need
0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18749810
No it will create one that has both
0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18749818
That should have been:

No it will create one that has both logins and users
0
 

Author Comment

by:ohemaa
ID: 18749826
did get some logins but not all
0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18749858
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
 

Author Comment

by:ohemaa
ID: 18749914
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
 
LVL 16

Accepted Solution

by:
rboyd56 earned 400 total points
ID: 18750086
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
 

Author Comment

by:ohemaa
ID: 18750143
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
 
LVL 16

Expert Comment

by:rboyd56
ID: 18750431
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
 

Author Comment

by:ohemaa
ID: 18750487
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
 
LVL 16

Expert Comment

by:rboyd56
ID: 18750580
I would think so
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question