limit rows for a user

Is it possible to set a limit for the number of rows to the user that connects to sql server (something like: whatever is the query this user does, set @.@.rowcount to 10000)? Thanks. Alberto

I don't think so unless you have access to the tool that this user is using.
@.@.rowcount being a connection specific setting, it must be set 'by the user' so to speak.

Depending on how the connection is done it may be a viable solution to sneak in a SET command somewhere?

/Kenneth

|||

One way to do this in SQL Server 2005 would be to use the TOP clause in view definition/SPs (this assumes that you are using one of these as primary data access mechanism). You can have a table that contains say the user and the number of rows. You can then add a TOP clause to your query like:

select TOP(select rowcnt from user_quota where user_name = SYSTEM_USER) ...

from ...

order by ...

You can use same technique in SQL Server 2000 in SPs and use SET ROWCOUNT.

|||Hi, thanks for your response, but the problem it's this:

our users using report builder are making reports containing several Mb of data (sometimes also 1Gb!). Now it happens that when 3-4 users do a report like that on the server the process w3wp.exe reaches also 10 Gb of memory allocated and then the whole reporting services istance stops working.

We want to limit the number of rows that can be extracted from the sql server db using report builder.

We cannot "touch" the way reporting services builds the queries (with several joins), all we need it's to set @.@.rowcount=10000 for the user (always th same) that reporting services uses to connect to sql server. In other words we need to execute a T-SQL command after user logon so that @.@.rowcount property it's set for every query the user makes.

Thanks.

Alberto|||You will have to ask this question in the Reporting Services forum then since it is not a TSQL issue. Maybe reporting services has some mechanism that will allow you to specify a pre-command.