Solved

Script users

Posted on 2007-03-19
15
420 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
so are these the 2 scripts that I need
0
 
LVL 16

Expert Comment

by:rboyd56
Comment Utility
No it will create one that has both
0
 
LVL 16

Expert Comment

by:rboyd56
Comment Utility
That should have been:

No it will create one that has both logins and users
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:ohemaa
Comment Utility
did get some logins but not all
0
 
LVL 16

Expert Comment

by:rboyd56
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I would think so
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 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

8 Experts available now in Live!

Get 1:1 Help Now