locked
Copy Roles, Logins & Accounts RRS feed

  • Question

  • Hi Folks,

    I wonder if someone can tell me whether the functionality to perform the following exists.  One of the guys here has a database which uses SQL Server Authentication - and so has assocciated roles and permissions on DB's defined. 

    He wants to swap to Windows Authentication.  So is there any way that this can be done? almost changing say from gkendall to DOMAIN\gkendall and retaining all roles, responsibilities and permissions??

    Many Thanks
    Graham
    Monday, November 2, 2009 3:43 PM

Answers

  • This can be done  but do remember each & every account in SQL server will have different SIDs . Possible thing that can be done in his case is , create new domain account and add it to sql server , change ownership for all objects and database held by gkendall to domain\gkendall . But even then the SID will not be the same , remember to change your connection string if some of them were created using sql authenticated login gkendall.
    Thanks, Leks
    Monday, November 2, 2009 3:55 PM
  • I'm pretty sure all you're going to want to do is remap the login and users.

    Here's what I'd do:
    1) Backup current login data with sp_help_revlogin
    2) Drop the current login i.e gkendall
    3) Create the new login i.e. DOMAIN\gkendall
    4) Map the new login to the existing database user -- exec sp_change_users_login 'updated_one', 'gkendall', 'DOMAIN\gkendall';

    Should work like a charm and if it doesn't you've got a backout plan with the original CREATE LOGIN already scripted for you with the password hash and SID. :)


    -Andrew
    • Proposed as answer by A.Lockwood Monday, November 9, 2009 3:13 AM
    • Marked as answer by Alex Feng (SQL) Tuesday, November 10, 2009 3:19 AM
    Monday, November 9, 2009 3:13 AM

All replies

  • This can be done  but do remember each & every account in SQL server will have different SIDs . Possible thing that can be done in his case is , create new domain account and add it to sql server , change ownership for all objects and database held by gkendall to domain\gkendall . But even then the SID will not be the same , remember to change your connection string if some of them were created using sql authenticated login gkendall.
    Thanks, Leks
    Monday, November 2, 2009 3:55 PM
  • I'm pretty sure all you're going to want to do is remap the login and users.

    Here's what I'd do:
    1) Backup current login data with sp_help_revlogin
    2) Drop the current login i.e gkendall
    3) Create the new login i.e. DOMAIN\gkendall
    4) Map the new login to the existing database user -- exec sp_change_users_login 'updated_one', 'gkendall', 'DOMAIN\gkendall';

    Should work like a charm and if it doesn't you've got a backout plan with the original CREATE LOGIN already scripted for you with the password hash and SID. :)


    -Andrew
    • Proposed as answer by A.Lockwood Monday, November 9, 2009 3:13 AM
    • Marked as answer by Alex Feng (SQL) Tuesday, November 10, 2009 3:19 AM
    Monday, November 9, 2009 3:13 AM