Link to home
Create AccountLog in
Avatar of PIERCGG
PIERCGGFlag for United States of America

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
Avatar of JestersGrind
JestersGrind
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of PIERCGG

ASKER

The same SQL LOGINS and USERS exist on both servers and SQL instances.  But how will this script fix the execute permissions on the stored procedures?  What is it doing?
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


>>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.
Avatar of PIERCGG

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
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.