Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2008
    Posts
    26

    Unanswered: Changing database user SID?

    Hi Guys,

    I migrate SQL Server 2000 db from one machine to another using the rev_loginhelp provide by Microsoft to copy the logins, all works perfectly for SQL Server Login, but notice when I log into the Server using a Window Login I can't access the db I have access to, I think it is because I am creating a new Window Login on the new machine and its creating a different SID . So I change the db user SID to the match this new SID, and it works...
    So my question is if it is OK to do this, is there another way to copy the Window Login?

    Thanks in advance.
    .....

  2. #2
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    [I'm guessing you ran the sp_rev_login to migrate your logins and backed up a database, both from the same source server? In theory, your logins and database users SHOULD be in sync but did you create the logins first and then restore the database on the target server? I think if the database didn't exist, rights for the logins may not have come across exactly right. I believe (and I don't have a test server to try this on, sorry) that if you restored the database and then created the logins, everything would've been fine. Basically I think the rights were "orphaned", not the logins/users.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I got this script from somewhere don't remember where. It addresses what you need.
    Attached Files Attached Files
    Last edited by rdjabarov; 08-05-08 at 16:42. Reason: file didn't get attached...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...wait, I knew I had one of my own...
    Attached Files Attached Files
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Aug 2008
    Posts
    26
    Thanks for your replies,

    I try to run rdjabarov script but am getting a error below....

    Server: Msg 103, Level 15, State 7, Procedure sp_ScriptWindowsLoginsAndPermissions, Line 64
    The identifier that starts with 'use [?];
    select 'use [?]' + char(13)+char(10) + 'go' + char(13)+char(10) + 'exec sp_grantdbaccess N''' + (case when (o' is too long. Maximum length is 128.
    Server: Msg 103, Level 15, State 1, Procedure sp_ScriptWindowsLoginsAndPermissions, Line 66
    The identifier that starts with 'use [?];
    select 'use [?]' + char(13)+char(10) + 'go' + char(13)+char(10) + 'exec sp_addrole N''' + name + ''', ''sa'''' is too long. Maximum length is 128.
    Server: Msg 103, Level 15, State 1, Procedure sp_ScriptWindowsLoginsAndPermissions, Line 68
    The identifier that starts with 'use [?];
    select 'use [?]' + char(13)+char(10) + 'go' + char(13)+char(10) + 'exec sp_addrolemember N''' + g.name + ''',' is too long. Maximum length is 128.
    Server: Msg 103, Level 15, State 1, Procedure sp_ScriptWindowsLoginsAndPermissions, Line 70
    The identifier that starts with 'use [?];
    select 'use [?]' + char(13)+char(10) + 'go' + char(13)+char(10) +
    case p.protecttype
    ' is too long. Maximum length is 128.
    Server: Msg 2812, Level 16, State 62, Line 1
    Could not find stored procedure 'dbo.sp_ScriptWindowsLoginsAndPermissions'.

    I've google for an answer and found out that I should download and install the latest version SQL Server 2000 Service Pack from the Microsoft website, I did this and the error still appears, any ideas?

    Thanks.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You need to change the maximum number of characters displayed in a column to something bigger than 256 which is the default. I usually set it to 8192 (the max value).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...and issue SET QUOTED_IDENTIFIER OFF before running the script.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •