Appropriate Use of READ UNCOMMITTED?

I haven't used the READ UNCOMMITTED transaction isolation level
before, and I was wondering if this would be an appropriate use:

I have an ID table containing ID numbers that are randomly generated
and need to be unique. There is a stored procedure that potentially
generates thousands of these IDs in one execution and inserts them
into the ID table and various other tables. The basic idea is as
follows:

Begin Transaction
While not all IDs generated {
GenID:
@.NewID = GenerateID()
If @.NewID exists in ID table
GOTO GenID

Insert into ID table
Insert into various other tables
}
Commit Transaction

The problem occurs when the stored procedure is being run by more than
one process concurrently. The check to see whether @.NewID exists in
the ID table will block, waiting for the transaction in the other
process to commit.

Would this be an appropriate place to use the READ UNCOMMITTED
isolation level to allow different executions of the stored procedure
to see what the others are writing into the ID table before the
transactions finish? I only really care that the IDs generated are
unique; they're not in sequence or anything like that. Has anyone had
experience with anything similar?Hi

If you read uncommitted then you have to be sure that if the writing
transaction rolls back there are no consequences for the process that reads
the (phantom) data that was uncommitted. It is not clear from your
description if you can generate the same key twice if the process rolls
back.

As both your processes will also be writing simulaneously they may well be
blocking regardless of the reads, therefore you may want to look at
shortening the transactions.

John

"Pham Nguyen" <sherkaner77@.yahoo.com> wrote in message
news:f682e0f6.0411200043.65e5059c@.posting.google.c om...
>I haven't used the READ UNCOMMITTED transaction isolation level
> before, and I was wondering if this would be an appropriate use:
> I have an ID table containing ID numbers that are randomly generated
> and need to be unique. There is a stored procedure that potentially
> generates thousands of these IDs in one execution and inserts them
> into the ID table and various other tables. The basic idea is as
> follows:
> Begin Transaction
> While not all IDs generated {
> GenID:
> @.NewID = GenerateID()
> If @.NewID exists in ID table
> GOTO GenID
> Insert into ID table
> Insert into various other tables
> }
> Commit Transaction
> The problem occurs when the stored procedure is being run by more than
> one process concurrently. The check to see whether @.NewID exists in
> the ID table will block, waiting for the transaction in the other
> process to commit.
> Would this be an appropriate place to use the READ UNCOMMITTED
> isolation level to allow different executions of the stored procedure
> to see what the others are writing into the ID table before the
> transactions finish? I only really care that the IDs generated are
> unique; they're not in sequence or anything like that. Has anyone had
> experience with anything similar?|||Pham Nguyen (sherkaner77@.yahoo.com) writes:
> I have an ID table containing ID numbers that are randomly generated
> and need to be unique. There is a stored procedure that potentially
> generates thousands of these IDs in one execution and inserts them
> into the ID table and various other tables. The basic idea is as
> follows:
> Begin Transaction
> While not all IDs generated {
> GenID:
> @.NewID = GenerateID()
> If @.NewID exists in ID table
> GOTO GenID
> Insert into ID table
> Insert into various other tables
> }
> Commit Transaction
> The problem occurs when the stored procedure is being run by more than
> one process concurrently. The check to see whether @.NewID exists in
> the ID table will block, waiting for the transaction in the other
> process to commit.

It would only block if you generate a duplicate. Assuming that is that
the id colunm is indexed, so you don't have to scan the table each time.

A better approach may be to to push the key generation out of the
transaction. That presumes that your business requirements can accept
that a key does not have any rows with it.

In fact, I have a procedure which generates a key for a set of work tables,
and that procedure barfs if it's called from within a transaction to
avoid contention problems.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95A85B954D69Yazorman@.127.0.0.1>...
> It would only block if you generate a duplicate. Assuming that is that
> the id colunm is indexed, so you don't have to scan the table each time.

That's true.

> A better approach may be to to push the key generation out of the
> transaction. That presumes that your business requirements can accept
> that a key does not have any rows with it.

That was how the procedure was originally written; the new keys were
put into a temporary table as they were generated, and then got copied
over inside of a transaction. The problem we encountered was that the
stored procedure generates potentially thousands of keys in one
execution. With two processes running the stored procedure
concurrently, we saw a lot of duplicate keys, and had to rollback.

> In fact, I have a procedure which generates a key for a set of work tables,
> and that procedure barfs if it's called from within a transaction to
> avoid contention problems.|||Pham Nguyen (sherkaner77@.yahoo.com) writes:
> That was how the procedure was originally written; the new keys were
> put into a temporary table as they were generated, and then got copied
> over inside of a transaction. The problem we encountered was that the
> stored procedure generates potentially thousands of keys in one
> execution. With two processes running the stored procedure
> concurrently, we saw a lot of duplicate keys, and had to rollback.

OK, so the keys has to be written to a table to be persisted. And this
may require a transaction, but the transaction should be committed here.

Here is a procedure that we use:

CREATE PROCEDURE ak_get_aidkey_sp @.aidkey int OUTPUT AS

DECLARE @.err int

-- Check transaction.
IF @.@.trancount > 0
BEGIN
RAISERROR('Internal error: to avoid contention issues, this procedure
must not be called from a transaction in progress.', 16, 1)
RETURN 55555
END

-- Aidkeys is supposed to be emptied once a day, so the below is likely
-- to generate a unique key at the first shot.
WHILE 1 = 1
BEGIN
SELECT @.aidkey = -1 * abs(checksum(newid()))

BEGIN TRANSACTION

IF NOT EXISTS (SELECT * FROM aidkeys (SERIALIZABLE)
WHERE aidkey = @.aidkey)
BEGIN
INSERT aidkeys (aidkey) VALUES (@.aidkey)
SELECT @.err = @.@.error IF @.err <> 0 RETURN @.err
BREAK
END

COMMIT TRANSACTION
END

COMMIT TRANSACTION

As you see, there is a transaction, but a very short one. Since you
generate many keys, you might need to modify the routine. Particularly,
if you generate 1000 keys in one go, the probability for at least one
collision increases.

A more brutal solution is to replace you current key column with a
uniqueidentifier and then use newid(). Then you can forget all about
collisions.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks - I have a few questions, though. What happens if the process
that is calling this procedure to generate keys errors out? Wouldn't
we want to be able to roll back the keys that have been generated?

Also, I'm not sure why the key table is emptied out every day, if the
keys have to be unique across days.

Is using READ UNCOMMITTED to scan the key table while generating keys
really bad? What sorts of problems can crop up?

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95A8B958CEC2AYazorman@.127.0.0.1>...

> OK, so the keys has to be written to a table to be persisted. And this
> may require a transaction, but the transaction should be committed here.
> Here is a procedure that we use:
>
> CREATE PROCEDURE ak_get_aidkey_sp @.aidkey int OUTPUT AS
> DECLARE @.err int
> -- Check transaction.
> IF @.@.trancount > 0
> BEGIN
> RAISERROR('Internal error: to avoid contention issues, this procedure
> must not be called from a transaction in progress.', 16, 1)
> RETURN 55555
> END
> -- Aidkeys is supposed to be emptied once a day, so the below is likely
> -- to generate a unique key at the first shot.
> WHILE 1 = 1
> BEGIN
> SELECT @.aidkey = -1 * abs(checksum(newid()))
> BEGIN TRANSACTION
> IF NOT EXISTS (SELECT * FROM aidkeys (SERIALIZABLE)
> WHERE aidkey = @.aidkey)
> BEGIN
> INSERT aidkeys (aidkey) VALUES (@.aidkey)
> SELECT @.err = @.@.error IF @.err <> 0 RETURN @.err
> BREAK
> END
> COMMIT TRANSACTION
> END
> COMMIT TRANSACTION
>
> As you see, there is a transaction, but a very short one. Since you
> generate many keys, you might need to modify the routine. Particularly,
> if you generate 1000 keys in one go, the probability for at least one
> collision increases.
> A more brutal solution is to replace you current key column with a
> uniqueidentifier and then use newid(). Then you can forget all about
> collisions.|||Pham Nguyen (sherkaner77@.yahoo.com) writes:
> Thanks - I have a few questions, though. What happens if the process
> that is calling this procedure to generate keys errors out? Wouldn't
> we want to be able to roll back the keys that have been generated?

That depends on your application. For our usage, this is perfectly
acceptable. If you want to roll back keys beause the transaction bailed
out, you will have to face a contention problem, since you cannot
commit until the keys have been used.

> Also, I'm not sure why the key table is emptied out every day, if the
> keys have to be unique across days.

Sorry, I forgot that our purpose is a bit special. We have a coupld of
so called aid-tables. They are permanent temp tables so to speak. That
is, they do hold transient data during some sort of process. They are
not temp tables because of performance problems, or because it's un-
suitable for the process for some other reason.

Our system has a night job, which can assume that when it runs, nothing
else runs in the database. One section in this night job, empties all
aid tables (in case there are some data left behind), as well as the
aidkeys table.

Obviously, if your keys are generated for a permanent purpose, you need
to maintain the table with the keys.

> Is using READ UNCOMMITTED to scan the key table while generating keys
> really bad? What sorts of problems can crop up?

Well, one problem is that two processes can get the aame key value.
That is, they both attempt the same key value, both find that it's not
in use, both try to insert, and only one will survive.

Have you considered uniqueidentifier? That is probably the easy way out.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95A9ED3EB8277Yazorman@.127.0.0.1>...

> Well, one problem is that two processes can get the aame key value.
> That is, they both attempt the same key value, both find that it's not
> in use, both try to insert, and only one will survive.

Is this prevented from happening with a higher isolation level?

> Have you considered uniqueidentifier? That is probably the easy way out.

Unfortunately, this is an existing application that's being modified
and the keys are already being used in other systems out there that we
don't have control over.

We may have to just live with the contention problems. The process in
question isn't real-time (it's part of a file upload process that
dumps data into our database) so we may be able to get away with it.|||Pham Nguyen (sherkaner77@.yahoo.com) writes:
> Erland Sommarskog <esquel@.sommarskog.se> wrote in message
> news:<Xns95A9ED3EB8277Yazorman@.127.0.0.1>...
>> Well, one problem is that two processes can get the aame key value.
>> That is, they both attempt the same key value, both find that it's not
>> in use, both try to insert, and only one will survive.
> Is this prevented from happening with a higher isolation level?

Yes, although for the point where you check whether a certain key value
is available, the default READ COMMITTED won't do. You need SERIALIZABLE
to hold a lock on the value which does not yet exist. Note that you
don't need SERIALIZABLE for the entire transaction, only for the query
where you check whether key is available.

(Depending on how these keys are assigned, it's possible that lower
levels will do, but as long as I don't know any details, I will have
to assume serializable.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Appropriate Use of End Conversation

I have a system that will post a message to a queue, but does not need to wait for a response - just needs to make sure the message arrived properly in the queue, not that is was processed at the receiving end. A second service will poll the queue to retrieve outstanding messages and will then move the message to an outside system. The movement of the message to the outside system will be wrapped in a transaction and if the process is successful, then the transaction will be commited otherwise it will be rolled back.

1) is it appropriate for the service that posts the message to send an END CONVERSATION ? This way the sending service will not be waiting for a response.

2) in the data movement phase, is it appropriate to issue and END CONVERSATION when commiting and not issue when ROLLBACK occurs. Or should ROLLBACK occur with a following END CONVERSATION with error message?

Can you define 'need to make sure the message arrived properly'?

In general a Service Broker application is guaranteed that the message send was properly delivered when the SEND statement was commited. That's the whole purpose of the broker, to take this responsibily out of the app programmer hands and offer it as 'plumbing'. A sent message should be considered 'delivered'. However, the application should not make assumptions about the timeframe of the said delivery. If the target service is down (i.e. for maintenance), if there is a bad configuration or simply transient errors, the message might stay 'in traffic' for seconds, minutes, maybe days. If the application needs a certain timeframe for delivery, then it should specify it as a conversation lifetime option in the BEGIN DIALOG statement.

And specific to your questions:

1) No. This is the dradded 'fire-and-forget' pattern. See this post here for details: http://blogs.msdn.com/remusrusanu/archive/2006/04/06/570578.aspx
The appropiate sequence of actions is for initiator to send the message and commit, then go on on its merry ways w/o waiting for a response. The target receives the message, process it and then ends the conversation. The EndDialog message is sent from the target to initiator and the initiator ends its side in reponse. The initiator ending of the conversation can be done by an activated procedure, so there's no need for initiator app to be hanging around waiting for a response.

2) None. You should never intentionally rollback receives from a queue. This is guaranteed to trigger poison message detection mechanism, which will deactivate the queue in response. An applications houls always be able to handle any message received. In case of an error, it should end the conversation with error then commit.

BTW, rather than use pooling on the target service, you should consider activation.

HTH,
~ Remus

|||

Both endpoints of a dialog (the initiator as well as the target) must end the dialog. It does not matter which service ends it first. The general rule is that whichever service has finished processing the dialog and is no longer interested in any messages the other side could send, should end the dialog first. On ending the dialog, the service will automatically send an EndDialog message to the other service so that it knows that the dialog has been closed at the remote end. On RECEIVing this message, the service would normally end the dialog on its end.

1) If your initiating service ends the conversation, it will not be able to receive any responses even asynchronously. Even if you do not have application-level responses, the response could be something as simple as ending the dialog at the target. There is no reason why you couldn't SEND your message from the initiating service and return without waiting for responses. You could setup an activated procedure on the initiator to simply receive EndDialog messages and end those dialogs. This would run asychronous to the thread doing the SEND.

2) All Service Broker statement (SEND, END CONVERATION, BEGIN DIALOG, etc) are transactional. That is, if you issue an END CONVERSATION and then rollback the transaction, the effect is as if the END CONVERSATION was never issued at all. In other words, if you want your END CONVERSATION to actually take effect, you must commit the transaction under which it is issued.

Finally, your app should not be relying on rolling back transactions for expected errors. For example, if you RECEIVE messages and then do some transactional work to an outside system which could fail, then rolling back the RECEIVE could have undesirable consequences. When you rollback a receive, the messages received are immediately put back in the queue and become available. If your app then loops around and re-issues a RECEIVE, it will receive the same set of messages. Trying to process the same message(s) may fail again. If this repeats 5 times, we detect a poisoned message and disable the queue. A better approach to handle expected errors is to remove the messages from the queue, saving them to a table and using some sort of retry mechanism (eg> conversation timers) to try again later.

|||

Thanks, I was unaware that the actual SEND message was transactional. That makes sense and helps me put everything into context. I'm still trying to get my mind around the service broker.

One question I do still have, in my case, the receiver will run several processes and then call a mySQL based system. If the mySQL based system is not available, then I would close the conversation with error. I guess I'd then place the message info into a staging table where further processing could be done on it. However, what if the info could not be placed into the staging table? I am confused as to how I could then regain access to this message to insure it gets ultimately fixed and written out to the correct system. Since its already been read successfully and a end conversation has been issued, the message is no longer available, correct?

|||

>> On ending the dialog, the service will automatically send an EndDialog message to the other service so that it knows that the dialog has been closed at the remote end. On RECEIVing this message, the service would normally end the dialog on its end.

Is the ending of the dialog automatic when a service receives a message that the other end of the conversation has ended the dialog or is this something that must explicitly be done after receiving the EndDialog message?

|||The END CONVERSATION must be issued explicitly by both services.

Appropriate Time Out for Alter Database?

Platform: SQL Server 2000 or higher

Scenario: We have an application which is going to perform some changes to a database, and for those changes to be safe, we want to set the database to single_user mode. We'll be executing commands like:

ALTER DATABASE <DbName> SET READ_ONLY WITH ROLLBACK IMMEDIATE

ALTER DATABASE <DbName> SET MULTI_USER

etc.

Is 15 seconds a reasonable timeout for the alter commands? Does database size come into play when considering the time out for such commands? Is there a good rule of thumb for this sort of thing?

What exactly are you changing in the database, or are you talking changing tables?

Generally, alter database and alter table are "safe" to do on a running database. You do not need to set it into single user mode.

Yes, using alter table, size of the table being altered is a factor in how long it will take to complete.

Using alter database is almost instant unless you are creating a new huge file.

|||

Ultimately, it's a large number of operations, which include adding data, removing data, altering table definitions, etc. It's a process to change a schema, and the potential quantity of items is sufficient that we want to make sure that none of our users can make any changes while the process is running, which is why we want to set it into single user mode.

It sounds like giving the alter database command 15 seconds is generous enough, then. In my tests, I've seen it take up to 2 seconds, but the database I was testing on is a little bit smaller than our average database.

|||In that case, you should just use a transaction. That way it will be all or nothing and it will lock what it needs to do.

|||

Well, the scope of the project I'm doing doesn't actually allow me to explore modifying this particular aspect of the solution. One of my tasks is to determine if a 15 second time out is reasonable or not, and having no luck finding the answer to that question anywhere else, I came here to see if there were any guidelines I could follow, for making that determination.

The "just use transaction" answer implies that there are no conceivable situations where it'd be appropriate to set the database to single user mode. I think that there probably are situations where it'd be appropriate, though I can't site them offhand. I'd probably need to ask more experienced SQL experts to give me an example where it'd be appropriate. It may be that my assumption is wrong, and that I should just be using transactions, and I guarantee that the next project I have where I can make that choice, I will use transactions. For this project, I'm required to set the database in single user mode, and I suspect that the only part of the TSQL that I need to worry about taking a long time is the "ROLLBACK IMMEDIATE," because that's going to depend on the number of transactions at the time that need to be rolled back.

|||I don't understand your question about "time" to change to single user mode. I don't know how you intend to run your alter database commands. Assuming you are running a script, if you either add the single user mode command to the beginning or make a batch file which ran osql and the alter database to single user, then the run the rest of the update script, then the updates would not run until the single user mode command was done. Or just run the command manually and wait for it to finish.

Yes, the rollback is what will take the time, it might even fail. I have had times, when it is unable to rollback very large transactions, and it just hangs. Depending on your usage and situation at the time, it might be 2 seconds or it make never succeed.

Appropriate Index

I have a table where data is deleted, inserted and updated. What kind of
index is appropriate for the column
where data is deleted ?
where data is inserted ?
where data is updated ?
Thanks for any input."DXC" <DXC@.discussions.microsoft.com> wrote in message
news:945F0AE5-AB17-4D18-8A35-312F4DB6E16F@.microsoft.com...
> I have a table where data is deleted, inserted and updated. What kind of
> index is appropriate for the column
> where data is deleted ?
> where data is inserted ?
> where data is updated ?
> Thanks for any input.
This depends on many things. If you post some DDL, we can help you out.
Read up on Indexes in the BOL for some tips on which columns are good
candidates for indexes and which ones are not.
Rick Sawtell
MCT, MCSD, MCDBA

Appropriate Index

I have a table where data is deleted, inserted and updated. What kind of
index is appropriate for the column
where data is deleted ?
where data is inserted ?
where data is updated ?
Thanks for any input.
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:945F0AE5-AB17-4D18-8A35-312F4DB6E16F@.microsoft.com...
> I have a table where data is deleted, inserted and updated. What kind of
> index is appropriate for the column
> where data is deleted ?
> where data is inserted ?
> where data is updated ?
> Thanks for any input.
This depends on many things. If you post some DDL, we can help you out.
Read up on Indexes in the BOL for some tips on which columns are good
candidates for indexes and which ones are not.
Rick Sawtell
MCT, MCSD, MCDBA

Appropriate Index

I have a table where data is deleted, inserted and updated. What kind of
index is appropriate for the column
where data is deleted ?
where data is inserted ?
where data is updated ?
Thanks for any input."DXC" <DXC@.discussions.microsoft.com> wrote in message
news:945F0AE5-AB17-4D18-8A35-312F4DB6E16F@.microsoft.com...
> I have a table where data is deleted, inserted and updated. What kind of
> index is appropriate for the column
> where data is deleted ?
> where data is inserted ?
> where data is updated ?
> Thanks for any input.
This depends on many things. If you post some DDL, we can help you out.
Read up on Indexes in the BOL for some tips on which columns are good
candidates for indexes and which ones are not.
Rick Sawtell
MCT, MCSD, MCDBA

Appropriate Data Type

I want to save some serialized data in a SQL Server 2005 table. What is the appropriate field type for this? I won't be associating a schema with the data. The data could vary in length--even up to several MB. I'm not sure whether to use text, NVARChar(MAX) or XML.

Brian

Brian,

A couple of SQL Server 2005 MSDN aritcles talk about when to use XML and some of the best practices. Some relevant ones are "XML Best Practices for Microsoft SQL Server 2005"; "XML Options in Microsoft SQL Server 2005"; "Performance Optimizations for the XML Data Type in SQL Server 2005".

In short XML is very useful 1) when you want schema validation (may not apply to your case); 2) when you want to query into the XML data or update granular parts of it. If your data is in XML format but your application merely uses the database to store and retrieve the data, an (n)varchar (max) column might suffice.

As a side note: Use varchar and nvarchar datatypes instead of text and ntext as the latter are in deprecation path (Deprecated Database Engine Features in SQL Server 2005)

Thanks

Babu

Application/Security Design: Stored Procedures versus SQL queries

Hello everyone,

I don't know what category would be appropriate for this question but security seems to be close enough.

I have this case scenario: I am running an automated application that extracts data from a web site and stores the data into a table on SQL server 2005. This information is not confidential in the extreme of social insurance #'s, bank account #s, but should not be seen by a typical employee (it has no use for them). After the data has been stored, it retrieves the data from the same table, processes it, and updates the same table. This application runs every hour infinitely.

Should all the insert, update, and select queries be stored under a stored procedure? I am not concern with performance. My concern would fall under design and security.

Is it worth to hide the details of inserting/updating/selecting behind a stored procedure? Or should I just allow the program to send select/update/insert SQL queries?

No employee (other then the developer and the DB admin) or customer ever access this table (They do not have permission from SQL). The username and passwords were created with security in mind.

Any thoughts or ideas?

Thanks for your time, Adrian

It sounds as though 'a typical employee' would not have access to the table. As long as the PUBLIC role cannot access the table, and 'a typical employee' does not have permissions, you are covered.

Using Stored Procedures 'may' be excessive in this situation.

|||

Thank your for your response.

I still am left wondering whether it is worth to hide the details of inserting/updating/selecting behind a stored procedure or whther I should just allow the program to send select/update/insert SQL queries.

I consider the stored procedure as the last "line of defense" when it comes to enforcing business rules. Although in theory of a 3-tier system, the business layer takes care of this, I have seen other developers program some sort of application and they forgot one aspect of the business or some sort of formating error. A stored procedure would correct this b/c it doesn't matter which business layer accesses the SQL server, they all will be enforced by the server.

Do you have an understanding of where I am coming from? Where do we draw the line of what the business layer enforces and what the SQL server enforced?

This is where I'd like to get other opinions on it too.

Thank you for your time, Adrian

|||

Adrian,

First, let me state that in 'almost' all situations, I recommend using stored procedures for a several reasons, including: reuseability, abstraction and security. Reuseabilitiy infurs that the same code is called multiple times from the application. Abstraction allows the procedure code (and how/where the data is actually stored) to be 'tuned' without having to redeploy the application. And of course, security protects the tables from inadvertent alteration and maintains a minimum level of data protection -and may even inject some form of audit trailing.

That said, I recommend against putting excessive 'business rules' into stored procedures. I prefer business rules to be in the 'middle tier' -mainly for scalability reasons, as well as my attempts to keep the data server dedicated to protecting the data. Here, however, you will find a great range of opinions, and as hardware becomes more robust and less expensive, and data clusters have become easier to configure and operate -'scalability' becomes less of an issue.

Now in your situation, you posit that the application is automatic -little chance for SQL injection, and users have no reason for direct data access. You have to balance the extra effort for creating and testing the stored procedures against the benefit. Granted, there 'may' be a slight increase in performance due to reusing compiled procedures -but you are doing the same activities over and over again so even the 'ad-hoc' queries would most likely be in the procedure cache (make sure the queries are properly parameterized). But the main question to ask is: "What is the benefit and what is the cost of mandating stored procedure use above direct table access?"

|||

May I know how to give all store procedures exec permission to one user instead one by one. Thanks in advance for your advice.

eg.

GRANT EXECUTE ON [dbo].[SP_xxx] TO [yyy]

GO

one by one procedure how can do it all for one go.

With regards

Bala

|||Create a database role. Add all users to the role. Grant permissions to the role.|||

Hi,

Thanks for the reply, we everyday delete the store procedure and recreate atleast few hundred. Once we delete the permission lost or new store procedures no permission set. If we know what is the command to run for all store procedures, instead of one by one i will run every time after creating the store procedure.

With regards

Bala

|||

I always recommend adding stored procedures to source control. Each stored procedure as a separate file. Databases can be 'refreshed' from source control in order to keep out development 'detritus'.

Each stored procedure file also contains the necessary permissions. Here is an example: (Works in SQL 2000/2005)

IF EXISTS
( SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'MyProcedureName'
)
DROP PROCEDURE dbo.MyProcedureName
GO

CREATE PROCEDURE dbo.MyProcedureName
/****************************************************************
* PROCEDURE: MyProcedureName
* DATE:
* AUTHOR:
*-
* DESCRIPTION:
*
*-
* CODE REVIEW: Date/Who/Status
*-
* VSS revision history: (location in source control)
****************************************************************/
( @.Parameter1 datatype,
, @.Parameter2 datatype
)
AS

SET NOCOUNT ON

-- Procedure Code Here

SELECT
@.Err = @.@.ERROR
, @.RowsAffected = @.@.ROWCOUNT

IF ( @.Err != 0 )
RETURN @.Err

IF ( @.RowsAffected = 0 )
RETURN -1

RETURN 0
GO

GRANT EXECUTE ON dbo.MyProcedureName TO MyCustomRole
GO

|||

Hi Arnie,

Thankyou very much i will do that.

With regards

Bala

Application/Security Design: Stored Procedures versus SQL queries

Hello everyone,

I don't know what category would be appropriate for this question but security seems to be close enough.

I have this case scenario: I am running an automated application that extracts data from a web site and stores the data into a table on SQL server 2005. This information is not confidential in the extreme of social insurance #'s, bank account #s, but should not be seen by a typical employee (it has no use for them). After the data has been stored, it retrieves the data from the same table, processes it, and updates the same table. This application runs every hour infinitely.

Should all the insert, update, and select queries be stored under a stored procedure? I am not concern with performance. My concern would fall under design and security.

Is it worth to hide the details of inserting/updating/selecting behind a stored procedure? Or should I just allow the program to send select/update/insert SQL queries?

No employee (other then the developer and the DB admin) or customer ever access this table (They do not have permission from SQL). The username and passwords were created with security in mind.

Any thoughts or ideas?

Thanks for your time, Adrian

It sounds as though 'a typical employee' would not have access to the table. As long as the PUBLIC role cannot access the table, and 'a typical employee' does not have permissions, you are covered.

Using Stored Procedures 'may' be excessive in this situation.

|||

Thank your for your response.

I still am left wondering whether it is worth to hide the details of inserting/updating/selecting behind a stored procedure or whther I should just allow the program to send select/update/insert SQL queries.

I consider the stored procedure as the last "line of defense" when it comes to enforcing business rules. Although in theory of a 3-tier system, the business layer takes care of this, I have seen other developers program some sort of application and they forgot one aspect of the business or some sort of formating error. A stored procedure would correct this b/c it doesn't matter which business layer accesses the SQL server, they all will be enforced by the server.

Do you have an understanding of where I am coming from? Where do we draw the line of what the business layer enforces and what the SQL server enforced?

This is where I'd like to get other opinions on it too.

Thank you for your time, Adrian

|||

Adrian,

First, let me state that in 'almost' all situations, I recommend using stored procedures for a several reasons, including: reuseability, abstraction and security. Reuseabilitiy infurs that the same code is called multiple times from the application. Abstraction allows the procedure code (and how/where the data is actually stored) to be 'tuned' without having to redeploy the application. And of course, security protects the tables from inadvertent alteration and maintains a minimum level of data protection -and may even inject some form of audit trailing.

That said, I recommend against putting excessive 'business rules' into stored procedures. I prefer business rules to be in the 'middle tier' -mainly for scalability reasons, as well as my attempts to keep the data server dedicated to protecting the data. Here, however, you will find a great range of opinions, and as hardware becomes more robust and less expensive, and data clusters have become easier to configure and operate -'scalability' becomes less of an issue.

Now in your situation, you posit that the application is automatic -little chance for SQL injection, and users have no reason for direct data access. You have to balance the extra effort for creating and testing the stored procedures against the benefit. Granted, there 'may' be a slight increase in performance due to reusing compiled procedures -but you are doing the same activities over and over again so even the 'ad-hoc' queries would most likely be in the procedure cache (make sure the queries are properly parameterized). But the main question to ask is: "What is the benefit and what is the cost of mandating stored procedure use above direct table access?"

|||

May I know how to give all store procedures exec permission to one user instead one by one. Thanks in advance for your advice.

eg.

GRANT EXECUTE ON [dbo].[SP_xxx] TO [yyy]

GO

one by one procedure how can do it all for one go.

With regards

Bala

|||Create a database role. Add all users to the role. Grant permissions to the role.|||

Hi,

Thanks for the reply, we everyday delete the store procedure and recreate atleast few hundred. Once we delete the permission lost or new store procedures no permission set. If we know what is the command to run for all store procedures, instead of one by one i will run every time after creating the store procedure.

With regards

Bala

|||

I always recommend adding stored procedures to source control. Each stored procedure as a separate file. Databases can be 'refreshed' from source control in order to keep out development 'detritus'.

Each stored procedure file also contains the necessary permissions. Here is an example: (Works in SQL 2000/2005)

IF EXISTS
( SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'MyProcedureName'
)
DROP PROCEDURE dbo.MyProcedureName
GO

CREATE PROCEDURE dbo.MyProcedureName
/****************************************************************
* PROCEDURE: MyProcedureName
* DATE:
* AUTHOR:
*-
* DESCRIPTION:
*
*-
* CODE REVIEW: Date/Who/Status
*-
* VSS revision history: (location in source control)
****************************************************************/
( @.Parameter1 datatype,
, @.Parameter2 datatype
)
AS

SET NOCOUNT ON

-- Procedure Code Here

SELECT
@.Err = @.@.ERROR
, @.RowsAffected = @.@.ROWCOUNT

IF ( @.Err != 0 )
RETURN @.Err

IF ( @.RowsAffected = 0 )
RETURN -1

RETURN 0
GO

GRANT EXECUTE ON dbo.MyProcedureName TO MyCustomRole
GO

|||

Hi Arnie,

Thankyou very much i will do that.

With regards

Bala