Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » SQL Server 2005 Security » Changing Logins DOMAIN for Users
Add to briefcase
12 posts, Page 1 of 2
12»»

Changing Logins DOMAIN for Users Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
mariann harper
Posted Tuesday, October 30, 2007 4:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 25, 2013 11:33 AM
Points: 18, Visits: 211
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).
Post #416822
ALZDBA
Posted Monday, November 05, 2007 2:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:17 AM
Points: 6,862, Visits: 8,049
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

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help

- 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
Post #418420
mariann harper
Posted Monday, November 05, 2007 2:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 25, 2013 11:33 AM
Points: 18, Visits: 211
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?
Post #418779
K. Brian Kelley
Posted Monday, November 05, 2007 7:02 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 1:13 PM
Points: 6,584, Visits: 1,790
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #418830
K. Brian Kelley
Posted Monday, November 05, 2007 7:04 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 1:13 PM
Points: 6,584, Visits: 1,790
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #418831
ALZDBA
Posted Tuesday, November 06, 2007 1:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:17 AM
Points: 6,862, Visits: 8,049
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

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help

- 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
Post #418912
mariann harper
Posted Tuesday, November 06, 2007 1:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 25, 2013 11:33 AM
Points: 18, Visits: 211
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:

http://www.mssqltips.com/tip.asp?tip=1063
Post #419276
K. Brian Kelley
Posted Tuesday, November 06, 2007 10:02 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 1:13 PM
Points: 6,584, Visits: 1,790
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #419431
the_skutts
Posted Thursday, June 12, 2008 12:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 11, 2013 3:11 PM
Points: 11, Visits: 389
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?
Post #516169
K. Brian Kelley
Posted Thursday, June 12, 2008 12:33 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 1:13 PM
Points: 6,584, Visits: 1,790
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #516178
« Prev Topic | Next Topic »

Add to briefcase
12 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.