Restrictions on Number of Records in a Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Restrictions on Number of Records in a Table

Author

Topic

augustin_p
Starting Member

21 Posts

Posted - 2002-04-15 : 08:06:06

i need an option to restrict the total number of records that can be inserted in a table.

For eg im developing a product whereby in demo version, he can add only 25 users to the table. i will be giving the DB Control to the user. so i dont want to use insert triggers 'cos the user might find them and delete it . Is there any option available to achieve in Sql Server 7.0 by using any other settings?

Thanks,
prasanna

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-15 : 08:17:31

The only method that comes to mind is to use a trigger. You can encrypt triggers; it may not prevent the trigger from being dropped, but it should prevent it from being modified.

Also consider revoking CREATE/ALTER TABLE privileges, and possibly CREATE/ALTER PROCEDURE privileges as well, unless these are absolutely required.

Edited by - robvolk on 04/15/2002 08:41:24

augustin_p
Starting Member

21 Posts

Posted - 2002-04-15 : 08:23:27

Hi Rob,
Are you sure that there is no other way except for using triggers?

Thanks,
prasanna

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-15 : 08:37:51

Nothing that can't be circumvented. Whenever restrictions are wanted in a SQL database, you either use permissions to control access to an object or column, constraints that can prevent certain actions, or triggers to determine whether an action should be rolled back.

The problem is that any constraint, foreign key or other restriction can be removed if the user has CREATE/ALTER TABLE privileges. The only way to enforce this kind of restriction is to revoke those privileges.

Edited by - robvolk on 04/15/2002 08:41:04

Nazim
A custom title

1408 Posts

Posted - 2002-04-15 : 08:44:52

As Rob has suggested you should revoke Create/Alter permissions. Allow the user access thru stored procedure only.

can create a sp to include the 25 records logic too

create procedure Insert_sp( @user varchar(3),.....)
as
declare @rCount int

select @rCount=count(1) from tablename where user=@user and version='Demo'

if @rCount=25
raiserror(16,1,'No furthur records can be inserted)

...

Go

--------------------------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources