November 28, 2007 at 3:17 am
I work for a business who uses SQL Server accross a small fleet of server machines. These servers have a realtime sync between primary and secondary sites, but something in this process seems to remove permissions on the secondary system. I dont have control of the sync (I've raised the problem with the person responsible), so cant fix what may be causing the issue. However, I would like to be able to guarentee the customers it wont happen again.
Is there a way to lock down permissions to tables and stored procedures so thay cant be removed without releasing them?
Can the same thing be applied to stored procedures and tables so the structures themselves cant be changed or deleted without first releasing them?
Such a feature would be perfect to protect our databases from "mistakes" which end up disabling customer web services.
Thanks for your time,
kinnon_2000
November 29, 2007 at 5:27 am
Hi,
You need to know what is happening during the sync process, how it's being done. You need to look at the source of the problem, to understand why the permissions are being removed before actually trying some solution.
Yvan
November 29, 2007 at 8:06 am
I agree with Yvan, but the short answer is you can't stop someone with sa or sysadmin permissions from changing the objects or permissions.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 29, 2007 at 9:05 am
ok folks. thanks for your help.
November 29, 2007 at 11:27 pm
However the "synch" is being accomplished may be the cause of your problems - for example if you're shipping a full backup of the database and restoring it on the target server, then forgetting to update/relink the user accounts (sp_change_users_login) that might explain it...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply