question

StuKay avatar image
StuKay asked

SQL Server users moving to new domain

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.
userdomains
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ozamora avatar image
ozamora answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

StuKay avatar image StuKay commented ·
It will only run on 2008 servers. Not 2000 or 2005.
0 Likes 0 ·
Shawn_Melton avatar image
Shawn_Melton answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.