September 30, 2009 at 10:15 am
Hi
SQL 2005.
I have a production server which I backup and restore to a test server.
However, whilst most of the stored procedures restore with the execute permissions, some new ones don't and I have to run the grants each time just for those procedures.
They are definitely there on the production but this is the only database where the permissions do not fully come over on restore for certain procedures.
September 30, 2009 at 11:55 am
You must be using an out of date backup. I've seen users missing on a restore because the login doesn't exist from one server to another or the login & user had an ID mismatch requiring synching, but if you're seeing differences inside the datatabase, it's because the backup is out of date or you're restoring the wrong one. Backup & restore just don't pick & choose which pieces of the restore or backup to run.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 30, 2009 at 3:04 pm
Thank you for your response Grant.
Sorry. It is from last night’s backup. It’s not out of date. I do check things before I ask for assistance. I think it may be due to a different compilation process. Some flag being changed.
September 30, 2009 at 4:58 pm
Object permissions are stored in system tables in each database, so it isn't really possible for the permissions to be different, because they are restored along with the rest of the database.
The most likely cause of the problem is that the logins on the source server don't match up with the logins on the target server. It is also possible that logins on the source server are sysadmin or the database owner on the source server but not on the target server.
September 30, 2009 at 6:59 pm
If the users are correctly matched to the logins... I still fall back on the backup is different than you think it is. Assuming the users are correctly in place in the database, all permissions that were backed up should be in place. There just isn't a setting that will cause it to not backup portions of the database while still successfully backing up other portions and restoring everything, again successfully. I agree with Micheal. It's much more likely that you've got a login/user disparity than an issue with specific stored procedure permissions. It just doesn't make sense based on how backups & restores work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 1, 2009 at 2:53 am
I think it is to do with login permissions. When this product was delivered, we were given a piece of sql to run after each restore.
Sp_change_user_login ‘update one ‘, ‘name’, ‘name’
The original stored procedures are ok, it seems to be only the new stored procedures which require the execute commands to be re run.
I will try to find an area where I can run a test restore for myself and see what comes over and when things change.
October 1, 2009 at 7:04 am
Hi
Sorry for wasting your time. Having done the restore, I was releasing to the developers who were dropping and recreating the stored procedures and then coming to me just stating the execute permissions were not there.
October 1, 2009 at 9:40 am
peter.lane (10/1/2009)
HiSorry for wasting your time. Having done the restore, I was releasing to the developers who were dropping and recreating the stored procedures and then coming to me just stating the execute permissions were not there.
Oh man, that's a classic!
It wasn't a waist of time. You got the problem solved. It wasn't any thing any one of us suggested, but you got it fixed. That's the important part. Thanks for letting us know what it was.
AND, I got a good laugh.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply