PIERCGG
asked on
User permissions don't work after a restore
I have two servers. If I do a backup on Server 1 and restore it to Server 2, the execute permissions that I have granted for users on the stored procedures do not work. Why?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
The assigned permissions are in the database that you restored. The problem is that the UID that is assigned to each user is different between the two servers, so the mapping between the SQL login at the server level and the database user is lost. That script will restore the mapping between the two. To verify that this is the issue, look at the properties of one of the database users. If you see a user name without a login name, it's an orphaned user.
Greg
Greg
>>What is it doing?
http://msdn.microsoft.com/en-us/library/ms174378.aspx
"Maps an existing database user to a SQL Server login. "
Greg has you on the right track w/ this, I'm pretty sure.
http://msdn.microsoft.com/en-us/library/ms174378.aspx
"Maps an existing database user to a SQL Server login. "
Greg has you on the right track w/ this, I'm pretty sure.
ASKER
The user exists AND the login exists. It is just the executable rights on the stored procedures that don't work (even though I can see the user in the PERMISSIONS section of the stored procedure properties). I have to take the user out. Put him back in and grant execute permission again. Then it works. So will the above script fix that?
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER