Grant access to a single table

So here's the situation: I'm creating a front-end application to an SQL Server 2000 database and I need to create a new login to the database for the app.

Is it possible (or even; is it a good idea...) to create a logon that only has access to a single table?

Actually, I know it's possible - but I don't want to have to go through the hundreds of tables and deny access to them all! So I guess the question is;

Is there a quick and easy way of granting a login permissions to a single table only?Aha! I think I might have cracked it...

Create a new database role and then a new login who can have that role :)

However, the question of "should I do this" still stands.|||Why not?

I don't understand what your concern is.

For a single logon, you could even grant the access directly without bothering with a role.|||Ooh, how do I do that?|||The same way you grant access to a role.

Remember, though, that every login is a member of the PUBLIC role, so you will need to make sure that PUBLIC does NOT have access to any of the objects in the database.|||How did I miss that button before?! Woods and trees, eh.

The public role only has access to sys tables and dt* SPs; I don't have a problem with that.

I just figured this was the easiest and most effective way of implementing security on the FE. This is definately a route I will use again in the future :)

Thanks Blinddudesql

Good Query for Paging?

Hi,

I am wondering what is the suggested query to use when I want to page through a bunch of data. I figure this is a pretty common situation so there should be some good ways to do this.

Basically I have a fixed page size and everytime a new one is requested, I just want to get those records. So if my page size is 10, for the first one I want to get records 1-10 of an ordered set, for page 2 I want to get records 11-20, etc.

The problem is I can only see "TOP" as an option, but that gives me the *first* n records. What if I want a set of records in the middle of the set?

I am also thinking some kind of subquery with an identity column, and then selecting based on the identity column may do the trick.

I would appreciate any any good solutions for this!

Thanks.select top 10 *
from (select top 20 * from tbl order by id) as a
order by id desc

For a variable page do it in dynamic sql.

or temp table solution - could use table variable)
create table #a (id int identity(1,1), ...)
set rowcount = @.maxrecs
insert #a select ...
set rowcount 0
select top 10 * from #a order by id desc
drop table #a

Global expression apply to all fields in report

Hi All,

I got a situation that need to write a expression for doing if the value is negtive then display () around this value, and this expression should apply to 30 fields in my report. so i just wonder is that any way that i can create this expression as global variable , then i can use this expression in each field, instead of i write IIF function in every field expression area.

Any helps are appreciated.

Cheers

Nick

Moved to SSRS forum....(from SSIS)|||

Hi Nick,

You can declare a public variable in the Custom code section of the SSRS reports. The syntax for this variable declaration should be VB. Once declared, it can be used in any part of the report by referring to the field as Code."Variable Name". eg. Code.IntCounter.

Regards,

Pradeep

|||

Yeah, i did this way, and it works.. Thanks mate

But i have a little question, it always hightlight the global variable name and indicate it is not declared object in expression area. even through it still works.

Cheers

Nick

Global expression apply to all fields in report

Hi All,

I got a situation that need to write a expression for doing if the value is negtive then display () around this value, and this expression should apply to 30 fields in my report. so i just wonder is that any way that i can create this expression as global variable , then i can use this expression in each field, instead of i write IIF function in every field expression area.

Any helps are appreciated.

Cheers

Nick

Moved to SSRS forum....(from SSIS)|||

Hi Nick,

You can declare a public variable in the Custom code section of the SSRS reports. The syntax for this variable declaration should be VB. Once declared, it can be used in any part of the report by referring to the field as Code."Variable Name". eg. Code.IntCounter.

Regards,

Pradeep

|||

Yeah, i did this way, and it works.. Thanks mate

But i have a little question, it always hightlight the global variable name and indicate it is not declared object in expression area. even through it still works.

Cheers

Nick