October 30, 2007 at 4:05 pm
We're a multi-facility operation, but due to a faulty raid controller, must move one location to a new DOMAIN. Thus, we have logins on SQL that have the old domain, but not the new domain. What's the fastest and easiest script to run to pull only LOGINS that have a specific DOMAIN listed, and also script the DATABASE ROLES and Database permissions they are assigned to, and do this across an entire SQL server for EVERY DATABASE?
I'm thinking there has to be a script out there that has a 'LIKE' statement in it that pulls all perms for the old domain users, and then I can do a search/replace to change the domain name from the old to the new one.
One problem I've run across with SQL 2000, is that if we keep the old domain and new domain logins, if the logins are the same (except for the domain name), you'll get an error if trying to add database permissions because it says the user already exists (it's seeing the old account with the old domain but we need to use both for awhile).
November 5, 2007 at 2:35 am
we just created the same userid in the new domain and imported the old domain's user SId in the SID-history list of that user in the new domain.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 5, 2007 at 2:52 pm
Our new domain does have the same user id's. However, I don't think I understand what you're saying about SQL. Updating the SID would fix the domain logins? I can't even add any of them manually to sql logins, because they say the login already exists if attaching them to a database. Could you enlighten me?
November 5, 2007 at 7:02 pm
He's talking about matching up SIDs domain-to-domain, not at the SQL Server level.
As to how to get the logins switched over, step 1, backup your master database. From there you can query syslogins (this works on SQL Server 2005 so you don't have to write separate scripts due to version) and build the sp_grantlogin scripts. For instance:
SELECT 'EXEC sp_grantlogin [' + REPLACE(name, ' ') + ']'
FROM syslogins
where isntname = 1
AND name LIKE ' %'
And you can do the same to build the sp_revokelogin script (which is why you backup the database, in case a mistake is made). After that, you run sp_change_users_login on each database and that should match everything up.
K. Brian Kelley
@kbriankelley
November 5, 2007 at 7:04 pm
It should be;
SELECT 'EXEC sp_grantlogin [' + REPLACE(name, 'old domain ', 'new domain') + ']'
FROM syslogins
where isntname = 1
AND name LIKE 'old domain%'
I tried to use tags and it didn't take.
K. Brian Kelley
@kbriankelley
November 6, 2007 at 1:12 am
the way we did it, it's just a windows thing.
each user has a SID-history in windows, apparently the current sid as well as the sid-history sids are being used to chalange a connection to sqlserver (off course windows authenticated).
This way, you don't have to do anything at sqlserver level, so no privileges will get lost and things will still work as before.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 6, 2007 at 1:19 pm
KB, I"ll try your method and hope that works on 2000. I was hoping it would be that simple, but everything I found on researching it produced all kinds of lengthy scripting, etc. I wonder if there will be a SIDS problem though. In that case, we'd have to do more, such as in this article:
November 6, 2007 at 10:02 pm
You will most likely have a SID issue. Unless your directory service admins ensured the SIDs were the same between the two domains (something that can be done during a migration), they aren't going to be because the SID is designed to be something which is unique.
That's why you have to script out the Windows logins for the old domains, do the domain replacement, and re-add them. When you do, SQL Server will have the correct SID, but this is still at the server level. You'll have to sync at the database level. This is where sp_change_users_login comes in. However, before you run it, you'll want to clean up the logins for the old domain. The database users will have their SIDs matching the old domain. Using sp_change_users_login once those old logins are cleaned up will allow you to associate those database users with the logins for the new domain.
If you are using different default databases other than master, you'll need to take that into account as the article says. You can script that out as well, or set it after you create the new logins, if there are only a handful you have to set.
K. Brian Kelley
@kbriankelley
June 12, 2008 at 12:18 pm
We are in process of doing this same effort. One question. sp_change_users_login appears to be specifically for SQL logins, not for Windows logins. Is there a way to sync a windows user sid (in sys.database_principals) to a new Windows login SID in sys.server_principals?
June 12, 2008 at 12:33 pm
Not through a stored procedure or anything like that. Are you switching domains? If so, you can probably script the permissions assigned directly to various Windows logins and re-apply them to roles. Then you could add in the new accounts.
K. Brian Kelley
@kbriankelley
June 12, 2008 at 12:53 pm
Yes, we are switching domains. New accounts are being created in the new domain with the SID history of the old account. Unfortunately, our application checks for a login (in sysxlogins) that matches the current user's windows login name rather than just allowing Windows to authenticate it. We would like to be able to add the 'new' Windows account in SQL, drop the old one, and map it to the existing user in each database. It sounds like we will need to create the new user in the databases as well and copy permissions from the old.
June 14, 2008 at 12:39 pm
Microsoft notes:
- "After migrating an account and maintaining the SID history of the source domain account, when a user logs on to the target domain, both the new SID and the original SID from the SID history attribute are added to the access token of the user and determine the local group memberships of the user. "
Because the migrated user account now has two SIDs in the DACL, it is implied that you shouldn't have a problem authenticating to SQL Server with the migrated account.
But I haven't personally tested this "theory".
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply