|
|
 |
|
|
|
|
SQL Server Domain Move Tips |
|
|
|
|
Back to
[
Technical Articles »
Microsoft SQL Server ]
Published Sep 11, 2005 @ 22:30:24
Domain Moves
About a year ago, the company I was working for was in the process of switching from NT domain structure (aka NT 4.0) to Active Directory. Part of the process required merging of multiple NT domains into one global, new, Active Directory domain. This proved to be quite the bourdon for those of us administrating the Microsoft SQL Servers in the building.
The Problem
The problem is simple: How can we convert from one domain to another for users that use NT authentication to access SQL Server, while keeping all of their current permissions?
When you grant access to an NT user in SQL Server, you (or Enterprise Manager) would execute a command similar to this:
exec sp_grantlogin 'NTDOMAIN\Username'
-- Set Default Database:
exec sp_defaultdb 'NTDOMAIN\Username', 'master'
-- do various per-database grants
Now your company has decided to move or rename from NTDOMAIN to ADDOMAIN. All of a sudden all of your SQL Server logins across all of your servers are invalid!
The Solution
The solution to this problem is not as simple as it seems like it should be. Worst of all, Microsoft refused to provide us with any type of actual solution to this problem. Even premium support tickets result in no answers. So I went exploring....
After discussions with one of the other DBAs, we decided that the best thing to do was grant the "new" user (old user, in a new domain) access to the database, set the same default database as their "old" user, and then update their SID in all of the user databases to match the SID of the "new" user, thereby preserving all of their permissions/roles. The syntax to do something like this is basically:
--
-- Create user [ADDOMAIN\Username] based on [NTDOMAIN\Username]
--
-- Grant login:
exec sp_grantlogin 'ADDOMAIN\Username'
-- Set Default Database:
exec sp_defaultdb 'ADDOMAIN\Username', 'corp'
-- Grant any server-roles:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Update user databases with newly mapped users:
update [corp]..[sysusers] set sid = isnull((select top 1 sid from master..syslogins where name = 'ADDOMAIN\Username'), sid) where sid = cast(0x010500000000000515000000CA9238E3847F9D439DCBD39204660000 as varbinary)
update [FeedbackPro_Test]..[sysusers] set sid = isnull((select top 1 sid from master..syslogins where name = 'ADDOMAIN\Username'), sid) where sid = cast(0x010500000000000515000000CA9238E3847F9D439DCBD39204660000 as varbinary)
update [GateKeeper]..[sysusers] set sid = isnull((select top 1 sid from master..syslogins where name = 'ADDOMAIN\Username'), sid) where sid = cast(0x010500000000000515000000CA9238E3847F9D439DCBD39204660000 as varbinary)
update [jontest]..[sysusers] set sid = isnull((select top 1 sid from master..syslogins where name = 'ADDOMAIN\Username'), sid) where sid = cast(0x010500000000000515000000CA9238E3847F9D439DCBD39204660000 as varbinary)
update [LiteSpeedCentral]..[sysusers] set sid = isnull((select top 1 sid from master..syslogins where name = 'ADDOMAIN\Username'), sid) where sid = cast(0x010500000000000515000000CA9238E3847F9D439DCBD39204660000 as varbinary)
update [LiteSpeedLocal]..[sysusers] set sid = isnull((select top 1 sid from master..syslogins where name = 'ADDOMAIN\Username'), sid) where sid = cast(0x010500000000000515000000CA9238E3847F9D439DCBD39204660000 as varbinary)
update [maintenance]..[sysusers] set sid = isnull((select top 1 sid from master..syslogins where name = 'ADDOMAIN\Username'), sid) where sid = cast(0x010500000000000515000000CA9238E3847F9D439DCBD39204660000 as varbinary)
update [NotePadNet]..[sysusers] set sid = isnull((select top 1 sid from master..syslogins where name = 'ADDOMAIN\Username'), sid) where sid = cast(0x010500000000000515000000CA9238E3847F9D439DCBD39204660000 as varbinary)
update [plm]..[sysusers] set sid = isnull((select top 1 sid from master..syslogins where name = 'ADDOMAIN\Username'), sid) where sid = cast(0x010500000000000515000000CA9238E3847F9D439DCBD39204660000 as varbinary)
update [tomtest]..[sysusers] set sid = isnull((select top 1 sid from master..syslogins where name = 'ADDOMAIN\Username'), sid) where sid = cast(0x010500000000000515000000CA9238E3847F9D439DCBD39204660000 as varbinary)
-- Completed creating/mapping user: [ADDOMAIN\Username]
The Script
Wow... what a mess! ...and this is just one user on one server! Well in the spirit of automation, we took the time and put together a script that would generate these commands for us. Also in the spirit of openness and sharing, I'm providing this script to the world, attached at the bottom of this article, to help with the transition from one domain to another. The script is named nt-to-active-directory.sql, but it can actually be applied to any domain move (within AD or NT)
Simply load the script in query analyzer on the main you want to convert, find these variables:
set @nt_domain = 'NTDOMAIN'
set @ad_domain = 'ADDOMAIN'
-- The @nt_domain is the old domain, and @ad_domain is the new domain.
Once you've made the changes, simply execute the script. The result in the query window will be a fully functioning and properly formatted set of commands to copy all users and update permissions in each of the user databases. Note: you may need to change to result to output as text instead of as a grid.
- Jon Coulter
Attachments:
» nt-to-active-directory.sql
[ 5,664 bytes ]
[ 1711 download(s) ]
Download this article as a PDF document.
Right Click and Choose "Save As..."
Post comments on this article in the
Forums.
|
|
