Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

comparsion of users between two servers Expand / Collapse
Author
Message
Posted Wednesday, January 11, 2012 9:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 2, 2015 8:16 PM
Points: 191, Visits: 444
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
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, February 15, 2016 6:24 AM
Points: 1,554, Visits: 289
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:11 AM
Points: 5,443, Visits: 12,961
Another option: use a linked server.

--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1234195
Posted Thursday, January 12, 2012 8:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:17 AM
Points: 10,784, Visits: 14,677
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 2, 2015 8:16 PM
Points: 191, Visits: 444
Thank you.
Post #1235802
Posted Friday, January 13, 2012 9:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 2, 2015 8:16 PM
Points: 191, Visits: 444
Is it a good Idea to link Production server to Test server?
Post #1235803
Posted Friday, January 13, 2012 9:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:11 AM
Points: 5,443, Visits: 12,961
Maybe not, but you could always do it the other way round.

--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1235807
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse