Data insertion limit on a user?

Question

Post reply

Data insertion limit on a user?

Del Piero

SSCrazy

Points: 2265
More actions
June 27, 2007 at 12:16 am

#117579

Hi,
Is it possible to pose a limit on how much data a user is allowed to insert in SQL Server? Like limit the number of rows he can insert on a table, or limit the amount (MBs) of data he is allowed to insert. It seems there is a space quota setting in Oracle, but not sure if it is possible in SQL Server (2000 or 2005)?
Thanks!
Del Piero

Viewing 6 posts - 1 through 5 (of 5 total)

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

I don't think so SQL Server has that much restirctions. You have to find a way out to do the same using windows quotas.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Chad Carter Ten Centuries Points: 1089 More actions · Answer 2

One option would be to create an Instead of Insert trigger on the needed tables. Inside the trigger you could evaluate the user and check how much data that user has already inserted. This would all be dependant on knowing which users have inserted each row.

Chad

Del Piero SSCrazy Points: 2265 More actions · Answer 3

Thanks for the replies, but I think the insert of triggers may not be easily implemented because, as you said, we need to trace which user has inserted what rows, and also this needs to be implemented on all the tables. In addition, we may want to see how much space the user has consumed instead of how many rows ........

Thanks anyway!

Del Piero

steve block SSCertifiable Points: 7698 More actions · Answer 4

What type of data are you talking about? Can the user name be stored in each row? Can the data be partitioned per table by user? i.e. have a dbo.mydata table and a user.mydata table for each user. Can the data be stored in separate databases for each user?

You have to have some mechanism to determine who inserted the data.

Steve

steve smith-401573 SSChampion Points: 10889 More actions · Answer 5

It sounds to me like you may be approaching this question from not precisely the correct angle.

Assumptions: 1. The database is a live production database. 2. The user may or may not be doing their job correctly [i.e., I'm not assuming the user is properly trained]. 3. The objective is NOT to measure just productivity, but to validate correct use of the system.

Whether I have the objective correct, or whether they're both correct, is not material. What matters is that if you are concerned about setting a quota, then resources are running awfully tight or a user is suspected of 'glutting' the database in some way. If you cannot trace transactions back to the user, then shame on the system / db designer(s)! If you can trace transactions back, with some kind of date/time stamp, then you simply need to implement an auditing process to monitor transaction volume by user. What you will do with that information is beyond the scope of this answer.