Solved

Script users

Posted on 2007-03-19
15
425 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL: Only Wanting One Record 8 60
Search Text in Views 2 27
t-sql left join 2 31
How can I group this data in the following manner? 2 27
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…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

734 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