Solved

Script users

Posted on 2007-03-19
15
421 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
  • 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now