Need Script for Automating Find and Fix Orphand users

Question

Need Script for Automating Find and Fix Orphand users

sam king

SSC-Addicted

Points: 479
More actions
July 23, 2014 at 11:34 am

#296734

Hi Friends,
I have 10 Databases .
Irefresh these databases from PROD to TEST server.
after refresh i have to fix the orphand users.
I need to automate the find and fix orphand users task.
Can any help me in getting the script for this.
Here i want to use update_one to fix the orphand user...bacause my task is to map the existing logins to the user...no need to create logins.
Breif information.
Test Server has nearly 30 databases in which i refresh 10 databases.
Step 1.Finding the orphand users
Step 2.fix orphand users using Update_One (Skip the error if login doesn't exist)
These steps to be repeated for all 10 databases.
need your help ......Pls reply friends
Regards,
SAM
Regards,
SAM
***Share your knowledge.It’s a way to achieve immortality----Dalai Lama***

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

MrG78 Default port Points: 1482 More actions · Answer 1

Hi Sam,

Are you able to recreate the logins on the TEST server so the SIDs are the same as the PROD? This would then remove the need to do this.

You probably already know but can reverse engineer CREATE LOGIN statements for logins that include their SID and hashed password using sp_help_revlogin, as per below article:

http://support.microsoft.com/kb/918992

Lowell SSC Guru Points: 323504 More actions · Answer 2

here's the basics of it: generate the orphans from the meta data:

/*--Results

ALTER USER [z_AppDB_reports] WITH LOGIN = [z_AppDB_reports];

ALTER USER [medical] WITH LOGIN = [medical];

*/

SELECT 'ALTER USER ' + quotename(dbloginz.name) + ' WITH LOGIN = ' + quotename(svloginz.name) + ';'

from sys.database_principals dbloginz

LEFT OUTER JOIN sys.server_principals svloginz

on dbloginz.name = svloginz.name

WHERE dbloginz.type IN ('S')

AND dbloginz.name NOT IN('dbo','guest','INFORMATION_SCHEMA','sys')

and svloginz.name is not null and dbloginz.sid <> svloginz.sid

to do it as a single script, server wide, you'd have to add a cursor i guess, but it's certainly doable.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

arnipetursson SSCertifiable Points: 6557 More actions · Answer 3

The best way to do this is to script out the users and their permissions while the test databases are in a good state.

Then:

1. restore from PROD

2. drop all prod users in the databases

3. create all the test users in the databases and grant them their permissions