|
Make sure that the connecting user only have access to the bare minimum he requires.
Such as only execute on sprocs and not access to change the sprocs, or direct access to the underlying tables.
|
|
|
|
|
<sarcasm>HAHAHA!! Of COURSE all users have very as-needed access to the DB! Our software vendor's thought of that!</sarcasm>
Of course, with any of my tables I try to keep things restricted to stored procs that certain users have access to.
Thanks for pointing this out, though. I appreciate any response.
|
|
|
|
|
I would be concerned about storing user names and passwords as plain text.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
In the DB, the user credentials are stored in the syslogins table, so there's at least that bit of security.
In most of my user side applications, I've been known to use a 1-1 and onto encryption to store credentials. I've been known to be lazy at times, however.
Fortunately, no one at this company is familiar with memory editors much less CLR reflection.
Thanks for the input.
|
|
|
|
|
Keep this in mind for your web apps. It doesn't apply to desktop apps because pooling is rarely an issue there.
Pool Fragmentation Due to Integrated Security
Connections are pooled according to the connection string plus the user identity. Therefore, if you use Basic authentication or Windows Authentication on the Web site and an integrated security login, you get one pool per user. Although this improves the performance of subsequent database requests for a single user, that user cannot take advantage of connections made by other users. It also results in at least one connection per user to the database server. This is a side effect of a particular Web application architecture that developers must weigh against security and auditing requirements.
http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx[^]
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
Hello all,
I want to write simle trigger in sql server for auto generate numbers. Can any one help me regarding this. and also want to know the Basic Structure of triggers in sql server 2005.Please help me regarding this.
Thanks
Rizana
|
|
|
|
|
|
|
Firstly, you don't need a trigger to auto generate numbers, have an identity column instead.
Secondly for the basic structure of a trigger try reading the help.
In fact, in light of your question, I suggest you read up on triggers before attempting to use them as you seem unclear about what they are for.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hello Mr.bob,
i want to write trigger only, because i want to generate number with Preceeding alphabets like 'a001' 'b002' for taht only i need triggers usually in my graduation i wrote trigger in oracle 9 i. but i dont know with the sql server. any way thanks for your suggestion. can i get the above with out trigger Please let me Know.
Thanks
RRiiizzzz
|
|
|
|
|
What is the benifit i get using stored procedure instead of tables in my web application using asp.net with c#
|
|
|
|
|
harish.k12 wrote: What is the benifit i get using stored procedure instead of tables in my web application using asp.net with c#
Security - If you provide access to the stored procs only and revoke access directly to the tables.
Consistent interface with the database - If you update your table structure it can easily break any applications that rely on the old structure. With stored procedures you get to define exactly what inputs and outputs are meaning that if you change your table structure the only other changes needed are to stored procedures (also in the database) so your applications will never need to be updated and redeployed.
|
|
|
|
|
... and performance. I know dynamic sql queries now get the query plan cached, but for anything slightly complex a stored procedure will consistently outperform dynamic sql. You are also (or should be) moving lots less data around the network - consider the select statment for a table of 25 columns compared to a simple "up_getaccount @accountid= 'abc'"
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ashfield wrote: and performance
Negligable these days... Also you can really screw up a sproc if it has a conditional statement with each path requiring a wildly different query plan. The first time the sproc is used it will cache the query plan. When it goes down the other path in subsequent calls it will be using an inefficient query plan.
|
|
|
|
|
Colin Angus Mackay wrote: if it has a conditional statement with each path requiring a wildly different query plan.
Should not be in the same proc then. Maybe have one main proc which checks the conditional variable and calls a further proc dependant on the value (which will then be properly optimised)
As for using stored procs, if you want more than a reasonably straight forward query there are far more opportunities for perfomance tuning in a proc than a piece of sql.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ashfield wrote: Should not be in the same proc then.
Absolutely - But is it something most people will think about?
Ashfield wrote: there are far more opportunities for perfomance tuning in a proc than a piece of sql.
Really? Isn't a stored proc just "a piece of sql"?
|
|
|
|
|
Colin Angus Mackay wrote: Absolutely - But is it something most people will think about
Well, if people don't think about performance it's their own fault.
Colin Angus Mackay wrote: Really? Isn't a stored proc just "a piece of sql"?
I was, I thought rather obviously, talking about real world code, where you frequently do a bit more than select a few fields from a couple of tables. For example taking a transactioon file of 2 million records and generating positions - try getting decent performance in something like that without stored procs. However, if you prefer to live in the world of CP type questions and believe that everything can be done with a few selects thats up to you - those of us that produce code for a living know the pros and cons of different methods but are always open to suggestion.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ashfield wrote: those of us that produce code for a living know the pros and cons of different methods but are always open to suggestion.
You don't sound very open to suggestion. It sounds like you are tying to insult me.
All I am saying is that a stored proc is just a piece of SQL. It happens to be stored in the database rather than have an application send it out. But, you can pack quite a lot in to a SqlCommand object if you have to. And if the SQL is highly changable I'm glad of that feature. It is, admittedly, not something I've put into production code, but if you need something quick, ad hoc, and throw away these features can be very useful.
Also, you can do just as much by sticking all the SQL into the SQL Command as you can by sticking the same SQL in a stored procedure. I also don't recommend doing that in a production environment. But those of us that produce code for a living know that sometimes you need something quick and dirty to get something going, such as a test environment or for a one time migration of data from one system to another.
At the end of the day you cannot simply say you have to use stored procedures for everything. You have to be open to the possibility that you can do the job with out them sometimes and it is acceptable to do so. You also have to know that they do make a lot of sense in many other scenarios.
|
|
|
|
|
Colin Angus Mackay wrote: You don't sound very open to suggestion. It sounds like you are tying to insult me.
Any insult was unintentional, I was merely trying to emphasise the difference between the college type of question we frequently get here on CP and the real world.
For a 'quick and dirty' we all do things we wouldn't put into production.
Colin Angus Mackay wrote: At the end of the day you cannot simply say you have to use stored procedures for everything.
As a long tme contractor, developing solutions in SQL, C#, VB.NET and ASP.NET, I have worked fr many clients (mostly blue chip financials), and the vast majority will not allow embedded sql, all database access is via stored procedures - for the very reason of change. Making a change to a stored procedure (which may then be replicated out) is far preferable and much quicker than co-ordinating an application roll out an to users worldwide for what may only be a one column change. As far as I am concerned embedding sql in a production strength application is an absolute no-no, but everyone to their own opinion.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ashfield wrote: I have worked fr many clients (mostly blue chip financials), and the vast majority will not allow embedded sql, all database access is via stored procedures
What, even if your application is just kicking of one-time migration scripts in a specific sequence?
|
|
|
|
|
Colin Angus Mackay wrote: What, even if your application is just kicking of one-time migration scripts in a specific sequence?
Here we have the difference. You see, I would not consider that a production app, although I do see what you mean - it running in a production environment. I consider a production app as one released out to users, running regularly, with all the overheads of releases. The situation described by you I would describe more as a utility program - one written to assist with a one off specific task. In this instance I would use (within reason obviously) whatever technique achieved the desired results in the shortest time.
Different perspectives make for misunderstandings I guess.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ashfield wrote: I would not consider that a production app
Fair enough. I see anything that runs on a live system as a production app - even if it is a one time thing like a migration process.
|
|
|
|
|
Ashfield wrote: those of us that produce code for a living know the pros and cons of different methods but are always open to suggestion.
That's an arrogant attitude you've got going for you there Bob. Before you try belittling somebody, perhaps you ought to take a look at who they are. Let's see - Colin; Microsoft MVP. CodeProject MVP 2005 through to 2008. Author of several well respected articles. Which bit of this suggests that he doesn't write code for a living to you? Well?
Perhaps you should have dialled back the attitude a bit and actually looked at what Colin had to say - rather than taking the chance to blow your own trumpet.
|
|
|
|
|
OK, apologies to any one who was offended or felt they were offended. I have to admit, I didn't look at Colin's profile, but as I explained in a subsequent post, I was trying to emphasise the difference between the college type environment and the real world, where you are dealing with large volumes of data and performing much more complicated operations.
However, once again I apologise if anyone was offended.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Everything you can do in the body of a stored proc you can do in a regular sql call, and the engine does a pretty good job at caching execution plans. Most stored proc performance benefits have been optimized away over the past few releases of SQL server.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
. 