Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Enforce SP permissions based on login permissons? Expand / Collapse
Author
Message
Posted Tuesday, December 31, 2013 7:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 11, 2014 4:43 AM
Points: 2, Visits: 9
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
Post #1526750
Posted Thursday, January 02, 2014 9:11 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:32 PM
Points: 1,356, Visits: 5,678
Haven't it used in sql server
Add a line with execute as?

Perhaps better:
DDL-triggers evaluating eventdata on INSERT/UPDATE/DELETE/MERGE/TRUNCATE...
http://technet.microsoft.com/en-us/library/ms173781.aspx
Post #1527178
Posted Friday, January 03, 2014 12:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 11, 2014 4:43 AM
Points: 2, Visits: 9
Cheers Jo, I'll take a look and see what I come up with.

Gaz
Post #1527363
Posted Friday, January 03, 2014 4:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 5:33 PM
Points: 251, Visits: 6,108
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.
Post #1527452
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse