Can I Restrict Values in a Parameter Dropdown based on the User?

I've got a puzzler here. I have a report with 4 different dropdown lists for
parameters. One of them is Department Id. For some users, I want them to be
able to only run the report against their own department id but still be able
to select any of the values for the other parameters. Other users will be
able to select any value for department id and the other parameters.
I can figure out how to write the query to populate the Department ID
dropdown but cannot figure how to access the current userid programatically.
Is there a way to access the user ID in order to restrict the values in a
parameter dropdown?
I'm using RS2003 with SQL Server 2000. Any ideas are welcome!
Thanks,
LizThis is really quite easy. There is a global parameter called User!UserID it
returns the domain and user ID of the user running the report. To see the
format of this create a report with a single textbox (no datasets). Set the
text box to an expression and set it to this (case sensitive):
=User!UserID
You might want to strip the domain off.
OK, the second part of this is that you need to realize that query parameter
and report parameter are two different things. You do not have to have a
query parameter mapped to a report parameter. It can be mapped to an
expression instead.
So, let's say you have this query
Select department from mydepartmentlist where userID = @.UserID
RS will create a report parameter for this called UserID. You will not use
it. You will delete it. Instead in the dataset tab click on the ... go to
the parameters tab, click on the dropdown box where the query parameter is
being mapped to report parameter and pick expression instead. Then set it to
the expression above. If you don't want the domain then use something like
this:
=mid(User!UserID, LengthToCutOffTheFront)
Of course for all of this to work you need a table somewhere that maps the
userid to the departments.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Liz" <Liz@.discussions.microsoft.com> wrote in message
news:5A54A060-B916-4BB4-AE03-9AF8302601A7@.microsoft.com...
> I've got a puzzler here. I have a report with 4 different dropdown lists
> for
> parameters. One of them is Department Id. For some users, I want them to
> be
> able to only run the report against their own department id but still be
> able
> to select any of the values for the other parameters. Other users will be
> able to select any value for department id and the other parameters.
> I can figure out how to write the query to populate the Department ID
> dropdown but cannot figure how to access the current userid
> programatically.
> Is there a way to access the user ID in order to restrict the values in a
> parameter dropdown?
> I'm using RS2003 with SQL Server 2000. Any ideas are welcome!
> Thanks,
> Liz
>|||Thanks! That's very helpful and I've already succeeded at stripping off the
domain name.
I know you can pass parameter values through the URL and query string to the
report. If I wanted to pass the department ID from the calling application
to populate the dropdown, is there a way to pass that one parameter value and
let the user select the other three?
Thanks for your help,
Liz
"Bruce L-C [MVP]" wrote:
> This is really quite easy. There is a global parameter called User!UserID it
> returns the domain and user ID of the user running the report. To see the
> format of this create a report with a single textbox (no datasets). Set the
> text box to an expression and set it to this (case sensitive):
> =User!UserID
> You might want to strip the domain off.
> OK, the second part of this is that you need to realize that query parameter
> and report parameter are two different things. You do not have to have a
> query parameter mapped to a report parameter. It can be mapped to an
> expression instead.
> So, let's say you have this query
> Select department from mydepartmentlist where userID = @.UserID
> RS will create a report parameter for this called UserID. You will not use
> it. You will delete it. Instead in the dataset tab click on the ... go to
> the parameters tab, click on the dropdown box where the query parameter is
> being mapped to report parameter and pick expression instead. Then set it to
> the expression above. If you don't want the domain then use something like
> this:
> =mid(User!UserID, LengthToCutOffTheFront)
> Of course for all of this to work you need a table somewhere that maps the
> userid to the departments.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Liz" <Liz@.discussions.microsoft.com> wrote in message
> news:5A54A060-B916-4BB4-AE03-9AF8302601A7@.microsoft.com...
> > I've got a puzzler here. I have a report with 4 different dropdown lists
> > for
> > parameters. One of them is Department Id. For some users, I want them to
> > be
> > able to only run the report against their own department id but still be
> > able
> > to select any of the values for the other parameters. Other users will be
> > able to select any value for department id and the other parameters.
> >
> > I can figure out how to write the query to populate the Department ID
> > dropdown but cannot figure how to access the current userid
> > programatically.
> > Is there a way to access the user ID in order to restrict the values in a
> > parameter dropdown?
> >
> > I'm using RS2003 with SQL Server 2000. Any ideas are welcome!
> >
> > Thanks,
> > Liz
> >
> >
>
>|||Sure. You have have a hidden parameter that gets filled in by the calling
application. The user only selects from the list you provide. Look at hidden
parameters in the books online.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Liz" <Liz@.discussions.microsoft.com> wrote in message
news:D0F372BF-DA85-4BD6-A01C-ED2708F14BAC@.microsoft.com...
> Thanks! That's very helpful and I've already succeeded at stripping off
> the
> domain name.
> I know you can pass parameter values through the URL and query string to
> the
> report. If I wanted to pass the department ID from the calling
> application
> to populate the dropdown, is there a way to pass that one parameter value
> and
> let the user select the other three?
> Thanks for your help,
> Liz
>
> "Bruce L-C [MVP]" wrote:
>> This is really quite easy. There is a global parameter called User!UserID
>> it
>> returns the domain and user ID of the user running the report. To see the
>> format of this create a report with a single textbox (no datasets). Set
>> the
>> text box to an expression and set it to this (case sensitive):
>> =User!UserID
>> You might want to strip the domain off.
>> OK, the second part of this is that you need to realize that query
>> parameter
>> and report parameter are two different things. You do not have to have a
>> query parameter mapped to a report parameter. It can be mapped to an
>> expression instead.
>> So, let's say you have this query
>> Select department from mydepartmentlist where userID = @.UserID
>> RS will create a report parameter for this called UserID. You will not
>> use
>> it. You will delete it. Instead in the dataset tab click on the ... go to
>> the parameters tab, click on the dropdown box where the query parameter
>> is
>> being mapped to report parameter and pick expression instead. Then set it
>> to
>> the expression above. If you don't want the domain then use something
>> like
>> this:
>> =mid(User!UserID, LengthToCutOffTheFront)
>> Of course for all of this to work you need a table somewhere that maps
>> the
>> userid to the departments.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Liz" <Liz@.discussions.microsoft.com> wrote in message
>> news:5A54A060-B916-4BB4-AE03-9AF8302601A7@.microsoft.com...
>> > I've got a puzzler here. I have a report with 4 different dropdown
>> > lists
>> > for
>> > parameters. One of them is Department Id. For some users, I want them
>> > to
>> > be
>> > able to only run the report against their own department id but still
>> > be
>> > able
>> > to select any of the values for the other parameters. Other users will
>> > be
>> > able to select any value for department id and the other parameters.
>> >
>> > I can figure out how to write the query to populate the Department ID
>> > dropdown but cannot figure how to access the current userid
>> > programatically.
>> > Is there a way to access the user ID in order to restrict the values in
>> > a
>> > parameter dropdown?
>> >
>> > I'm using RS2003 with SQL Server 2000. Any ideas are welcome!
>> >
>> > Thanks,
>> > Liz
>> >
>> >
>>

Can I restrict members of db_datareader from SELECTing from a tabl

I have a large database with lost of users, many of whom are in the
db_datareader role.
I have a table that I want to restrict SELECTs on to just sa's.
How can I restrict members of db_datareader from SELECTing from this table?
As shown below, I cannot REVOKE or DENY SELECT from this role.
I could remove everyone from db_datareader and put them in a special role
with SELECT on everything but this table. But is there an easier way?
Thanks
USE admin
CREATE TABLE myTable
(col1 int PRIMARY KEY IDENTITY
,col2 int
,col3 int)
INSERT myTable (col2) VALUES (10)
INSERT myTable (col2) VALUES (20)
INSERT myTable (col2) VALUES (30)
SELECT * FROM myTable
execute as login = 'appuser'
select suser_name()
SELECT * FROM myTable
revert
sp_helprolemember db_datareader
REVOKE SELECT ON myTable TO db_datareader
--Cannot grant, deny or revoke permissions to or from special roles.
DENY SELECT ON myTable TO db_datareader
Hello,
You can not attempt to issue a DENY or REVOKE on to a fixed role.Issue the
DENY SELECT statement on to all uses who are not supposed to
query the table and see if that works well.
THanks
Hari
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:B4980135-A8AE-4D4B-8B11-2623407D8986@.microsoft.com...
>I have a large database with lost of users, many of whom are in the
> db_datareader role.
> I have a table that I want to restrict SELECTs on to just sa's.
> How can I restrict members of db_datareader from SELECTing from this
> table?
> As shown below, I cannot REVOKE or DENY SELECT from this role.
> I could remove everyone from db_datareader and put them in a special role
> with SELECT on everything but this table. But is there an easier way?
> Thanks
> --
> USE admin
> CREATE TABLE myTable
> (col1 int PRIMARY KEY IDENTITY
> ,col2 int
> ,col3 int)
> INSERT myTable (col2) VALUES (10)
> INSERT myTable (col2) VALUES (20)
> INSERT myTable (col2) VALUES (30)
> SELECT * FROM myTable
> execute as login = 'appuser'
> select suser_name()
> SELECT * FROM myTable
> revert
> sp_helprolemember db_datareader
> REVOKE SELECT ON myTable TO db_datareader
> --Cannot grant, deny or revoke permissions to or from special roles.
> DENY SELECT ON myTable TO db_datareader
>
>
|||Hi Dave
"Dave" wrote:

> I have a large database with lost of users, many of whom are in the
> db_datareader role.
> I have a table that I want to restrict SELECTs on to just sa's.
>
It sounds like you need to make your security more granular, by creating
your own role(s) that only have the permissions the users actually need. You
could also do this using Windows Groups (or a combination of both!) if you
are using Windows Authentication. By only using Stored Procedures to access
your data it may be possible to limit who can SELECT from a table further.
John

Can I restrict members of db_datareader from SELECTing from a tabl

I have a large database with lost of users, many of whom are in the
db_datareader role.
I have a table that I want to restrict SELECTs on to just sa's.
How can I restrict members of db_datareader from SELECTing from this table?
As shown below, I cannot REVOKE or DENY SELECT from this role.
I could remove everyone from db_datareader and put them in a special role
with SELECT on everything but this table. But is there an easier way?
Thanks
--
USE admin
CREATE TABLE myTable
(col1 int PRIMARY KEY IDENTITY
,col2 int
,col3 int)
INSERT myTable (col2) VALUES (10)
INSERT myTable (col2) VALUES (20)
INSERT myTable (col2) VALUES (30)
SELECT * FROM myTable
execute as login = 'appuser'
select suser_name()
SELECT * FROM myTable
revert
sp_helprolemember db_datareader
REVOKE SELECT ON myTable TO db_datareader
--Cannot grant, deny or revoke permissions to or from special roles.
DENY SELECT ON myTable TO db_datareaderHello,
You can not attempt to issue a DENY or REVOKE on to a fixed role.Issue the
DENY SELECT statement on to all uses who are not supposed to
query the table and see if that works well.
THanks
Hari
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:B4980135-A8AE-4D4B-8B11-2623407D8986@.microsoft.com...
>I have a large database with lost of users, many of whom are in the
> db_datareader role.
> I have a table that I want to restrict SELECTs on to just sa's.
> How can I restrict members of db_datareader from SELECTing from this
> table?
> As shown below, I cannot REVOKE or DENY SELECT from this role.
> I could remove everyone from db_datareader and put them in a special role
> with SELECT on everything but this table. But is there an easier way?
> Thanks
> --
> USE admin
> CREATE TABLE myTable
> (col1 int PRIMARY KEY IDENTITY
> ,col2 int
> ,col3 int)
> INSERT myTable (col2) VALUES (10)
> INSERT myTable (col2) VALUES (20)
> INSERT myTable (col2) VALUES (30)
> SELECT * FROM myTable
> execute as login = 'appuser'
> select suser_name()
> SELECT * FROM myTable
> revert
> sp_helprolemember db_datareader
> REVOKE SELECT ON myTable TO db_datareader
> --Cannot grant, deny or revoke permissions to or from special roles.
> DENY SELECT ON myTable TO db_datareader
>
>|||Hi Dave
"Dave" wrote:
> I have a large database with lost of users, many of whom are in the
> db_datareader role.
> I have a table that I want to restrict SELECTs on to just sa's.
>
It sounds like you need to make your security more granular, by creating
your own role(s) that only have the permissions the users actually need. You
could also do this using Windows Groups (or a combination of both!) if you
are using Windows Authentication. By only using Stored Procedures to access
your data it may be possible to limit who can SELECT from a table further.
John

Can I restrict members of db_datareader from SELECTing from a tabl

I have a large database with lost of users, many of whom are in the
db_datareader role.
I have a table that I want to restrict SELECTs on to just sa's.
How can I restrict members of db_datareader from SELECTing from this table?
As shown below, I cannot REVOKE or DENY SELECT from this role.
I could remove everyone from db_datareader and put them in a special role
with SELECT on everything but this table. But is there an easier way?
Thanks
USE admin
CREATE TABLE myTable
(col1 int PRIMARY KEY IDENTITY
,col2 int
,col3 int)
INSERT myTable (col2) VALUES (10)
INSERT myTable (col2) VALUES (20)
INSERT myTable (col2) VALUES (30)
SELECT * FROM myTable
execute as login = 'appuser'
select suser_name()
SELECT * FROM myTable
revert
sp_helprolemember db_datareader
REVOKE SELECT ON myTable TO db_datareader
--Cannot grant, deny or revoke permissions to or from special roles.
DENY SELECT ON myTable TO db_datareaderHello,
You can not attempt to issue a DENY or REVOKE on to a fixed role.Issue the
DENY SELECT statement on to all uses who are not supposed to
query the table and see if that works well.
THanks
Hari
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:B4980135-A8AE-4D4B-8B11-2623407D8986@.microsoft.com...
>I have a large database with lost of users, many of whom are in the
> db_datareader role.
> I have a table that I want to restrict SELECTs on to just sa's.
> How can I restrict members of db_datareader from SELECTing from this
> table?
> As shown below, I cannot REVOKE or DENY SELECT from this role.
> I could remove everyone from db_datareader and put them in a special role
> with SELECT on everything but this table. But is there an easier way?
> Thanks
> --
> USE admin
> CREATE TABLE myTable
> (col1 int PRIMARY KEY IDENTITY
> ,col2 int
> ,col3 int)
> INSERT myTable (col2) VALUES (10)
> INSERT myTable (col2) VALUES (20)
> INSERT myTable (col2) VALUES (30)
> SELECT * FROM myTable
> execute as login = 'appuser'
> select suser_name()
> SELECT * FROM myTable
> revert
> sp_helprolemember db_datareader
> REVOKE SELECT ON myTable TO db_datareader
> --Cannot grant, deny or revoke permissions to or from special roles.
> DENY SELECT ON myTable TO db_datareader
>
>|||Hi Dave
"Dave" wrote:

> I have a large database with lost of users, many of whom are in the
> db_datareader role.
> I have a table that I want to restrict SELECTs on to just sa's.
>
It sounds like you need to make your security more granular, by creating
your own role(s) that only have the permissions the users actually need. You
could also do this using Windows Groups (or a combination of both!) if you
are using Windows Authentication. By only using Stored Procedures to access
your data it may be possible to limit who can SELECT from a table further.
John