db owner

Question

Post reply

db owner

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

sqlfriends (8/12/2014)
Is CodeSmith one of them?
Thanks

Yes. An older topic on the discussion:

http://www.sqlservercentral.com/Forums/Topic1375474-391-1.aspx

It's short, but the following google search should also lead you in the right direction for looking over different options:

https://www.google.com/search?site=&source=hp&q=SQL+Server+CRUD+procedure+generator&oq=SQL+Server+CRUD+procedure+generator

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 2

dan-572483 (8/12/2014)
There is no right or wrong answer to this question.
The advantage of accessing data through storied procedure only include
1) Security. If a user is in db_datareader role and the knows how to use other tools that can access SQL, like Excel, Microsoft access, they will be able to query anything in the database, or write bad queries that may cause performance issues. Maybe they need this access to the data and this is fine, and maybe they don't. By using exclusively stored procedures for data access security can be designed to users will only have access to the data and queries designed in the stored procedures. There is principle of minimal permissions that says users should have rights to do what's required for their jobs and nothing else.
2) Stored procedures are useful when application and database developers are separate people with separate skill sets. A .NET developer may not know much about SQL queries and vice-versa. Stored procedures allow a SQL developer to worry about SQL queries and interface developers to focus on the interface and much of the application logic.
On the other hand...
Some applications contain reporting or querying functions that give a lot of flexibility to users to define what they want to see. Giving users more access to the database allows users to get what they want easily without making every new query a development project requiring the cooperation of a team of people.
So it really depends on the environment, the needs of users and security needs.

You forgot some important points such as abstraction and performance. 😉 And not just performance of the code, either. A stored procedure that provides the correct abstraction can easily be updated, tested, and deployed without having to do a "build" for the front end.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlfriend SSC Guru Points: 52769 More actions · Answer 3

sqlfriend

SSC Guru

Points: 52769

August 13, 2014 at 9:45 am

#1737666

Thanks all, this helps me a lot.

dan-572483 SSCoach Points: 15870 More actions · Answer 4

Jeff Moden (8/12/2014)
You forgot some important points such as abstraction and performance. 😉 And not just performance of the code, either. A stored procedure that provides the correct abstraction can easily be updated, tested, and deployed without having to do a "build" for the front end.

Good point. A stored procedure may be optimized, debugged or modified with breaking or modifying the application, provided the parameter requirements and output format are not changed.