We are in the process of moving all of our users from Domain A to Domain B. We have around thirty five SQL servers mainly in Domain A and a few in Domain B (The servers are to remain in situ for now but will all move to Domain B at some point in the future). There are over 200 databases spread across these servers (2000, 2005, 2008). Access to these databases varies between Windows and SQL Server Authentication. I think I only need to concentrate on those users who connect via Windows Authentication. I hope to eventually create a script for each server to duplicate the existing users with their new domain login. I can centrally gather information on logins from all the servers (via Sys.Server_Principals and master.dbo.syslogins) but I am having difficulty in getting the relevant role and system privilege information for each user/database combination on each server. The information is quite easy to gather if you log on to each server/database and run the relevant queries but it would be helpful if I could just run a script from one server to assemble all the relevant information in one central point via linked servers. The problem seems to be the system tables I would expect to exist at server level only exist at database level E.g. sys.sysmembers etc. Has anyone got any constructive views or ideas on this? With the time scales I am working under it looks as though I will have to visit each server individually and manually create the new logins.
Not sure if this will work with 2000, but an alternative will be to use SQL 2008 Central Management Server and run a script to extract users and roles on each server. The script will run across all servers registered.
I'm not sure since this question was asked back in 2010, if it still relavent but here goes... My suggestion on gathering your information is to use PowerShell. As long as you have PowerShell v2 running on all your servers, utilizing remote access can allow you to connect to each instance (SQL 2005 and 2008 only) with SMO. Depending on the number of SQL 2000 instances you actually have you would could remotely execute the old fasion SQL script and osql.exe.