Sql permissions change on 2005 upgrade

Question

Sql permissions change on 2005 upgrade

Stubby Bunny

SSCommitted

Points: 1733
More actions
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

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

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

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

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

Stubby Bunny SSCommitted Points: 1733 More actions · Answer 2

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.

Rich Hansell SSCrazy Points: 2346 More actions · Answer 3

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.

george sibbald SSC Guru Points: 104210 More actions · Answer 4

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

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

Stubby Bunny SSCommitted Points: 1733 More actions · Answer 5

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.

george sibbald SSC Guru Points: 104210 More actions · Answer 6

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.

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

DCarlson Say Hey Kid Points: 688 More actions · Answer 7

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