February 13, 2013 at 3:00 pm
I have contractors that write Stored Procedures and users that run them as part of applications. I don't want to grant either of the groups dbowner to the primary database, datareader only.
At the same time I am told that Stored Procedures are faster than running the queries from the applications.
Is this a practical solution: I create an empty DB same name but _procs then all of the Stored Procedures are created and run here but they read from the primary database. I grant DBOWNER to this _procs DB and datareader to the primary DB.
Any drawbacks to this?
This also helps me isolate Stored Procedures written from apps like SSRS rather than burying them in the primary DB with applicaiton specific Stored Procedures.
February 13, 2013 at 3:03 pm
dcyoung (2/13/2013)
I have contractors that write Stored Procedures and users that run them as part of applications. I don't want to grant either of the groups dbowner to the primary database, datareader only.At the same time I am told that Stored Procedures are faster than running the queries from the applications.
Is this a practical solution: I create an empty DB same name but _procs then all of the Stored Procedures are created and run here but they read from the primary database. I grant DBOWNER to this _procs DB and datareader to the primary DB.
Any drawbacks to this?
This also helps me isolate Stored Procedures written from apps like SSRS rather than burying them in the primary DB with applicaiton specific Stored Procedures.
You don't need to make them a member of dbowner. Just need to grant them the ability execute the procs.
GRANT EXECUTE ON [OBJECT] TO
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 14, 2013 at 11:45 am
That helps with part of my problem. I have a third party application hosted in house. We have read only to the DB period. I need to pull data and would prefer to use stored procedures over leaving sql in code so I thought I would create an empty DB in same instance and create stored procedures there. The stored procedures will read from the primary DB. This way I can create and grant execute to the stored procs but still get data from the primary DB.
Will there me any performance or other issues I can expect to bump into.
February 14, 2013 at 12:31 pm
dcyoung (2/14/2013)
That helps with part of my problem. I have a third party application hosted in house. We have read only to the DB period. I need to pull data and would prefer to use stored procedures over leaving sql in code so I thought I would create an empty DB in same instance and create stored procedures there. The stored procedures will read from the primary DB. This way I can create and grant execute to the stored procs but still get data from the primary DB.Will there me any performance or other issues I can expect to bump into.
Are you saying the DB is readonly or that your service agreement says you can't modify it? As long as your other database is on the same server and you use fully qualified object names, you shouldn't have any issues performance wise.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 14, 2013 at 12:59 pm
Service agreement says we can't modify. Thanks for the reply. That is kind of what I though but it's nice to get a second opinion.
February 14, 2013 at 1:19 pm
dcyoung (2/14/2013)
Service agreement says we can't modify. Thanks for the reply. That is kind of what I though but it's nice to get a second opinion.
Given the service agreement that is probably the best way to do it. If you only need to pull data a view might be better than a stored proc.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply