Post reply

Sql permissions change on 2005 upgrade

  • December 2, 2008 at 6:05 am

    #202171

    Having a problem with Sql 2005 permissions when we upgrade from 2000. What we are doing is building a 2005 box and then moving over the databases. So in a sense its not really upgrading as much as just moving over DB from 2000 to 2005. The problem we are having is it is cutting out the permissions on the DB's when we do it. How can we aviod this???? Thanks

  • December 2, 2008 at 6:24 am

    #906174

    can you expand on the error a bit? Are the users losing all access to the database? As you are moving the databases to a new server the users may becoming orphaned from their logins. this is most likely if you are using SQL authentication.

    check out sp_change_users_login with the update_one option

    ---------------------------------------------------------------------

  • December 2, 2008 at 6:52 am

    #906196

    What is happening is we are moving DB from 2000 to 2005 and we have user accounts that loose all permissions to the DB's they are attached to. We use the user accounts for access from our Dot net programs.

  • December 2, 2008 at 6:57 am

    #906200

    I am assuming you created the server level logins on the 2005 box. My first guess is that the logins in the DBs are not matching the server level login sids. try running this

    sp_change_users_login Auto_Fix, 'userID'. It should fix the issue.

  • December 2, 2008 at 7:01 am

    #906203

    have you transferred the logins as well? sp_help_revlogin is best for this in your scenario, will avoid need for sp_change_users_login as it brings across the sid as well. there is a version for 2000to2005

    ---------------------------------------------------------------------

  • December 2, 2008 at 7:08 am

    #906208

    If I am reading from the last to posts correctly it sounds as if the logins sids that are attached to the DBs are not matching the original sids that where associated with them and that;s why permissions where lost. Is this correct.

  • December 2, 2008 at 7:13 am

    #906211

    kevin.deutschman (12/2/2008)

    If I am reading from the last to posts correctly it sounds as if the logins sids that are attached to the DBs are not matching the original sids that where associated with them and that;s why permissions where lost. Is this correct.

    thats our theory, correct. so you need to match the users back to their logins. If you run sp_helpuser in the database and loginname shows up as null that would suggest this is the case.

    ---------------------------------------------------------------------

  • December 3, 2008 at 9:07 am

    #906956

    Rich Hansell (12/2/2008)

    I am assuming you created the server level logins on the 2005 box. My first guess is that the logins in the DBs are not matching the server level login sids. try running this

    sp_change_users_login Auto_Fix, 'userID'. It should fix the issue.

    You can also run

    EXEC sp_change_users_login 'REPORT';

    to list all users that are orphaned from their associated logins.

    Check out the following for more information:

    Regards

    ----------------------------------------------------------------------------
    "No question is so difficult to answer as that to which the answer is obvious." - George Bernard Shaw

Viewing 8 posts - 1 through 7 (of 7 total)

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