comparsion of users between two servers

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2008 » SQL Server Newbies » comparsion of users between two servers

Add to briefcase

comparsion of users between two servers

Expand / Collapse

Author

Message

Posted Wednesday, January 11, 2012 9:39 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:37 PM
Points: 170, Visits: 388

Can we compare users between two servers, for ex: users in production and test environments to see users in the Test Environment that are not in production and inactive Production users that are active in the Test environment.

Post #1234131

Posted Wednesday, January 11, 2012 9:56 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 12:46 PM
Points: 1,493, Visits: 239

Sure you can, but you have a little work to do.

This query lists users per database :
sp_MSForeachdb '
USE ?
select
db_name() as dbname
, sp.name as login
, dp.name as [user]
from sys.database_principals dp
inner join sys.server_principals sp on sp.principal_id = dp.principal_id
WHERE dp.type_desc = ''SQL_USER''
'

Run it on your test and prod server and then :
- compare with Excel for eample
- import your prod users to your dev server (you will have to store the results in a table and then use Import Data wizard in Management Studio)
and compare with a FULL JOIN

Post #1234156

Posted Wednesday, January 11, 2012 10:42 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:29 AM
Points: 4,804, Visits: 8,067

Another option: use a linked server.

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1234195

Posted Thursday, January 12, 2012 8:41 AM

SSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:22 PM
Points: 10,571, Visits: 11,871

A third option is to use PowerShell. I'm not a PowerShell guy myself, but I think this is an excellent scenario for using it.

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #1234897

Posted Friday, January 13, 2012 9:40 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:37 PM
Points: 170, Visits: 388

Thank you.

Post #1235802

Posted Friday, January 13, 2012 9:40 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:37 PM
Points: 170, Visits: 388

Is it a good Idea to link Production server to Test server?

Post #1235803

Posted Friday, January 13, 2012 9:45 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:29 AM
Points: 4,804, Visits: 8,067

Maybe not, but you could always do it the other way round.

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1235807

« Prev Topic | Next Topic »

Add to briefcase

Permissions

Expand / Collapse