December 31, 2013 at 7:51 am
Hi all,
Wonder if someone can help. I've recently set up a SQL server that we'll be using purely for reporting purposes; its currently replicating data from our main production database which is all good. I've created a new login on the new server with read only access to ensure no-one manages to update/insert etc into the subscriber. Again, all good so far, the problem I'm having is when we get to stored procedures and functions - I'm having to grant execute permissions to the SPs and functions that different reports needs. This means I have to check through them to ensure there are no updates/inserts etc before granting the execute permissions. I was hoping someone might know a clever way to give the read only user permission to run all SPs/functions but for the user's permission to be taken into account rather than allowing the SP/function to do as is pleases....does that make sense? Essentially if the SP/function only performs reads then cool carry on but if the SP/function amends data then throw a fuss.
Any help is appreciated 🙂
Thanks
Gaz
January 3, 2014 at 12:29 am
Cheers Jo, I'll take a look and see what I come up with.
Gaz
January 3, 2014 at 4:53 am
I've been faced with a similar issue to this before. In the end created a new empy database and then created synonyms for all the tables referencing the original database. The I recreated all the stored procedures and functions in the new database. These were then using the syonyms. Granted execute permissions to the stored procedures and functions in the new database to the read-only login and then granted only SELECT permissions in the original database to the read-only login. Luckily the stored procedures and functions in the source database did not change often. All the stored procedures that did select only worked fine, any updates, inserts or deletes got rejected.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply