Post reply

Moving from SQL Logins to Windows NT logins

  • June 11, 2004 at 12:29 pm

    #87694

    I am working on moving most of our SQL Server logins to Windows NT logins.  Some of the SQL Server logins are owners of objects within the database.  I have been trying to figure out an easier way to change the ownership of the object to the new Windows login.  For example:  user1 is a SQL Server login and is the owner of several tables & SPs in the Datawarehouse database.  I add the windows login domain\user1 and now I want to get rid of the sql login user1.  I can't figure out how to get rid of the sql login and have the ownership transfer to the new windows login.  I experiemented with the sp_SidMap SPs but was not able to get it to map a SQL Server login to a window login. It would only go SQL Server to SQL Server.  Has anyone crossed this bridge already and care to share how you accomplished it?  I would be forever greatful! 

    Thanks!

    John

  • June 14, 2004 at 8:00 am

    This was removed by the editor as SPAM

  • June 18, 2004 at 5:14 am

    #510985

    mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsqlref.chm::/ts_sp_ca-cz_1lpu.htm

     

    As Discussed in this BookS Online URL, use sp_changeobjectowner to change the object owner.

    Dont forget to script the permissions, and get the permissions back.

    First,

    Create a new database -then- Simulate the existing permissions for the same User1, -then- apply permissions -then- script permissions applied - then- Change object owner using sp_changeobjectowner -then- translate sql user1 to Win user1 -then- Apply Permission -then- Verify if all permissions are done.

    Once u are confident about the above series of steps, Roll it out to Production system.

    Cheers-Gan.

  • June 18, 2004 at 7:47 am

    #511027

    Do you have a lot of objects that refer to other objects (views, stored procedures, functions)? If so, do they specify the owner name when they reference the object (owner.object like dbo.mytable)?

    To get the permissions, you can use sp_helprotect and script to reapply permissions when you change the owner with sp_changeobjectowner, because as Gan says, all permissions will be dropped.

    The biggest headache is, of course, to get all the object references.

    K. Brian Kelley
    @kbriankelley

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply