January 11, 2012 at 9:39 am
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.
January 11, 2012 at 9:56 am
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
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
January 11, 2012 at 10:42 am
Another option: use a linked server.
-- Gianluca Sartori
January 12, 2012 at 8:41 am
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
Consultant - Straight Path Solutions
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
January 13, 2012 at 9:40 am
Thank you.
January 13, 2012 at 9:40 am
Is it a good Idea to link Production server to Test server?
January 13, 2012 at 9:45 am
Maybe not, but you could always do it the other way round.
-- Gianluca Sartori
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply