Solved

Script users

Posted on 2007-03-19
15
422 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql 2014,  lock limit 5 32
Query Syntax 17 36
SQL server vNext 18 30
Correct an issue with a where clause with calculation 2 31
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

831 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