filter by UserID

I have many reports that are filtered by the userID of the person running
the report. Aside from rolling my own security is there a way for me to use
RS to pass this ID. Since the user is logging in via Windows Authentication
I have their login name. I was thinking of using this to query the UserID
from a custom table and then use that as a hidden parameter for all the
sp's. The problem I have though is how to get that UserId prior to anything
on the report happening.
Thanks,
ShawnThe user!userid global variable can be used as a input to your query. Do the
following, create a query parameter. Click on the ..., go to parameters and
then map the query parameter to the global variable (it has domain so you
might want to strip off the domain). To map it choose expressions and that
brings up the expression builder. Next go to layout, report parameters and
remove the parameter that was automatically created for you by RS when you
created the query parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Shawn Mason" <shawn@.issda.com> wrote in message
news:%23YFHG1L$EHA.3416@.TK2MSFTNGP09.phx.gbl...
> I have many reports that are filtered by the userID of the person running
> the report. Aside from rolling my own security is there a way for me to
use
> RS to pass this ID. Since the user is logging in via Windows
Authentication
> I have their login name. I was thinking of using this to query the UserID
> from a custom table and then use that as a hidden parameter for all the
> sp's. The problem I have though is how to get that UserId prior to
anything
> on the report happening.
> Thanks,
> Shawn
>|||Here's something I did, to strip off the domain, as Bruce says:
SELECT e.EMPLID, e.NAME
FROM EMPLTABLE e
WHERE (e.ID = RIGHT(@.UserID, LEN(@.UserID) - CHARINDEX('\', @.UserID)))
It will use whatever you write after the \ in a domain\username scenario.
Kaisa M. Lindahl
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OiMDkHM$EHA.3908@.TK2MSFTNGP12.phx.gbl...
> The user!userid global variable can be used as a input to your query. Do
the
> following, create a query parameter. Click on the ..., go to parameters
and
> then map the query parameter to the global variable (it has domain so you
> might want to strip off the domain). To map it choose expressions and that
> brings up the expression builder. Next go to layout, report parameters and
> remove the parameter that was automatically created for you by RS when you
> created the query parameter.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Shawn Mason" <shawn@.issda.com> wrote in message
> news:%23YFHG1L$EHA.3416@.TK2MSFTNGP09.phx.gbl...
> > I have many reports that are filtered by the userID of the person
running
> > the report. Aside from rolling my own security is there a way for me to
> use
> > RS to pass this ID. Since the user is logging in via Windows
> Authentication
> > I have their login name. I was thinking of using this to query the
UserID
> > from a custom table and then use that as a hidden parameter for all the
> > sp's. The problem I have though is how to get that UserId prior to
> anything
> > on the report happening.
> >
> > Thanks,
> >
> > Shawn
> >
> >
>

Filter a Model Table?

I am using RS 2005. I am setting up a Model for use within Report Builder so our clients can write their own reports.

A 2 part question, simple question first:

1. How can I filter the records in a Model Entity? I thought this would be possible from a Perspective but it is not.

2. How can I filter the records in a Model Entity based on the locale of the person who is logged on? And also based on their permissions?

TIA

You are correct that this is not possible with Perspectives. Note that Perspectives do not secure your data in any way.

You can use Model Item Security and Security Filters to expose different records to different users. A security filter could include a formula filter condition that uses the GETUSERCULTURE function. However, this seems a little odd, since culture is trivial to “spoof”, so it doesn’t really secure anything.

--Bob

|||Thanks for the reply Bob.

So I am in SQL Server Management Studio, I have double clicked on the model and gone to Model Item Security. But, I cannot see where to set up a Security Filter? Also, when I seach on SQL Server 2005 BOL for "Security Filter" it finds nothing.

|||Hi, can someone please help me with this ... please. People keep talking about Security Filter Scripts but I cannot find them anywhere ...|||Ummm, well can someone then please just tell me if this is a difficult question? I have seen a few other posts on the same question and the answer is never posted. Can someone at MSFT please just put me out of my misery and tell me either how to use this facility or, even better, refer me to the associated documentation or, not so good, that the feature is a figment of our collective imaginations. :)|||SecurityFilters is a collection property of a model entity. You "turn on" security filters by adding at least one filter attribute to this collection (i.e. if this collection is empty, security filters are "off" and all users with permission to the entity will be able to see all rows). Each filter in the collection defines a set of rows to which a user or group may be granted access. You can grant access to a specific user or group by giving them permission to see the filter attribute using Model Item Security. Users will have access to the UNION of all rows exposed by the security filters for which they have permission. Note 1: Filter attributes are typically used only for security filters, so the Hidden property is usually set to true. Note 2: Model entities also have a DefaultSecurityFilter property which can be used to grant access to some set of rows for users that do not have access to any of the filter attributes in the SecurityFilters collection.|||I am afraid this is getting very frustrating for me. Your answer here sounds very nice Bob - but it does not tell me how to do it. Also if I search google and microsoft for SecurityFilters or look for books I find no further help. I have a book on Report Services 2000, but obviously it does not cover of Report Models (a 2005 feature).

I am using v8.0.50215.44 of Visual Studio 2005. And I am using v9.00.1187.00 of Microsoft SQL Server Management Studio. Maybe these versions are too old?

If I open up the model designer and click on a model entity to see it's properties, then where do I add in collection properties for the model entity? Especially, how can I say it is a filter attribute? I can see no such properties.

TIA|||I'm sorry this has been frustrating. Let me try to fill in some of the gaps here.
SQL 2005 documentation ("SQL Books Online") is not available on the web yet. It sounds like you have the July CTP build installed; I'm not sure what state the documentation was in back then. Even if you can find the SecurityFilters property, it may have just been stub docs at that point.
If you want, you can download the September CTP docs here . RTM bits (including docs) are now available to MSDN subscribers, and will be publicly available after the launch next week.
SecurityFilters is a property on a model entity. You should see it near the end of the list in the property grid in Model Designer. Like several other model entity properties (e.g. IdentifyingAttributes, DefaultDetailAttributes, SortAttributes), it contains a set of references to model attributes you have previously defined. The only constraint on this particular collection is that all attributes referenced by it must have DataType=Boolean and IsFilter=True. The easiest way to create such an attribute is to select the entity on which it will be defined, right-click on an empty area of the attribute list, and choose New->Filter.
Hopefully this is enough to help you get started.|||Many thanks Bob! Yes, the problem has been that I have had the July CTP build. That info looks perfect to get me started - I appreciate your help.

I'll go get the Septmeber CTP build and doco now and work from there.

I'm looking forward to the release date - I am registered for the one day launch here in NZ - should be grand! :)|||Current books online still has very few things to say about secufity filters on report models. Does anyone know of any sites or articles that would greatly explain how to do this?|||

Hi Bob,

Your article on http://msdn2.microsoft.com/en-us/library/ms365343.aspx is very helpful, but when I use SQL Server Management Studio to set Model Item Security for differnt security filters, it seems "Permissions" property surpass "Model Item Security" property.

For example, in "Permissions" property of the model, if I checked "Use these roles for each group or user account" without setting any user or group, no matter what users I added to "Model Item Security" with "Secure individual model items independently for this model" checked, NO one user can see the model on report manager and report builder;

in above situation, if I added "user1" and gave role such as "Browser" role to "user1" in "Permissions" property, if I checked "Secure individual model items independently for this model" in "Model Item Security" property, even I did NOT grant "user1" to root model and any entities under the model, the "user1" is able to access the model and all entities in report builder.

My question is on the same report model, how to set "AdminFilter" (empty security filter) for administrator permissions and set "GeneralFilter" (filtered on UserID) for general user based on their UserID?

I posted my issue on http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1905805&SiteID=17, I attach my post here:

"

I read the following on article http://msdn2.microsoft.com/en-us/library/ms365343.aspx:

"Security filters are always applied, even for users who have Content Manager or Administrator permissions to the model. To allow administrators or other users to see all rows of an entity on which row-level security is defined, you can create an empty security filter (which always returns True) and then use the filter to grant those users access to all the rows."

So I defined 2 filters "GeneralFilter" and "AdminFilter" for "Staff" entity for my report model "SSRSModel", I expect after I deployed the report model, the administrator users use report builder to build reports with all rows available, and the non-admin users can only see rows based on their UserID.

I can only get one result at a time but not both:

either the rows are filtered or not filtered at all, no matter how I set the "SecurityFilter" for the entity: I tried setting both "AdminFilter" and "GeneralFilter" for SecurityFilter at the same time, combination of "DefaultSecurityFilter" and "SecurityFilter", or one at a time.

Anybody please please help me? Thank you!

"

Report server is using Custom Authentication.

Thank you for your help.

Temple1

Filter a Model Table?

I am using RS 2005. I am setting up a Model for use within Report Builder so our clients can write their own reports.

A 2 part question, simple question first:

1. How can I filter the records in a Model Entity? I thought this would be possible from a Perspective but it is not.

2. How can I filter the records in a Model Entity based on the locale of the person who is logged on? And also based on their permissions?

TIA

You are correct that this is not possible with Perspectives. Note that Perspectives do not secure your data in any way.

You can use Model Item Security and Security Filters to expose different records to different users. A security filter could include a formula filter condition that uses the GETUSERCULTURE function. However, this seems a little odd, since culture is trivial to “spoof”, so it doesn’t really secure anything.

--Bob

|||Thanks for the reply Bob.

So I am in SQL Server Management Studio, I have double clicked on the model and gone to Model Item Security. But, I cannot see where to set up a Security Filter? Also, when I seach on SQL Server 2005 BOL for "Security Filter" it finds nothing.|||Hi, can someone please help me with this ... please. People keep talking about Security Filter Scripts but I cannot find them anywhere ...|||Ummm, well can someone then please just tell me if this is a difficult question? I have seen a few other posts on the same question and the answer is never posted. Can someone at MSFT please just put me out of my misery and tell me either how to use this facility or, even better, refer me to the associated documentation or, not so good, that the feature is a figment of our collective imaginations. :)|||SecurityFilters is a collection property of a model entity. You "turn on" security filters by adding at least one filter attribute to this collection (i.e. if this collection is empty, security filters are "off" and all users with permission to the entity will be able to see all rows). Each filter in the collection defines a set of rows to which a user or group may be granted access. You can grant access to a specific user or group by giving them permission to see the filter attribute using Model Item Security. Users will have access to the UNION of all rows exposed by the security filters for which they have permission. Note 1: Filter attributes are typically used only for security filters, so the Hidden property is usually set to true. Note 2: Model entities also have a DefaultSecurityFilter property which can be used to grant access to some set of rows for users that do not have access to any of the filter attributes in the SecurityFilters collection.|||I am afraid this is getting very frustrating for me. Your answer here sounds very nice Bob - but it does not tell me how to do it. Also if I search google and microsoft for SecurityFilters or look for books I find no further help. I have a book on Report Services 2000, but obviously it does not cover of Report Models (a 2005 feature).

I am using v8.0.50215.44 of Visual Studio 2005. And I am using v9.00.1187.00 of Microsoft SQL Server Management Studio. Maybe these versions are too old?

If I open up the model designer and click on a model entity to see it's properties, then where do I add in collection properties for the model entity? Especially, how can I say it is a filter attribute? I can see no such properties.

TIA|||I'm sorry this has been frustrating. Let me try to fill in some of the gaps here.
SQL 2005 documentation ("SQL Books Online") is not available on the web yet. It sounds like you have the July CTP build installed; I'm not sure what state the documentation was in back then. Even if you can find the SecurityFilters property, it may have just been stub docs at that point.
If you want, you can download the September CTP docs here . RTM bits (including docs) are now available to MSDN subscribers, and will be publicly available after the launch next week.
SecurityFilters is a property on a model entity. You should see it near the end of the list in the property grid in Model Designer. Like several other model entity properties (e.g. IdentifyingAttributes, DefaultDetailAttributes, SortAttributes), it contains a set of references to model attributes you have previously defined. The only constraint on this particular collection is that all attributes referenced by it must have DataType=Boolean and IsFilter=True. The easiest way to create such an attribute is to select the entity on which it will be defined, right-click on an empty area of the attribute list, and choose New->Filter.
Hopefully this is enough to help you get started.|||Many thanks Bob! Yes, the problem has been that I have had the July CTP build. That info looks perfect to get me started - I appreciate your help.

I'll go get the Septmeber CTP build and doco now and work from there.

I'm looking forward to the release date - I am registered for the one day launch here in NZ - should be grand! :)|||Current books online still has very few things to say about secufity filters on report models. Does anyone know of any sites or articles that would greatly explain how to do this?|||

Hi Bob,

Your article on http://msdn2.microsoft.com/en-us/library/ms365343.aspx is very helpful, but when I use SQL Server Management Studio to set Model Item Security for differnt security filters, it seems "Permissions" property surpass "Model Item Security" property.

For example, in "Permissions" property of the model, if I checked "Use these roles for each group or user account" without setting any user or group, no matter what users I added to "Model Item Security" with "Secure individual model items independently for this model" checked, NO one user can see the model on report manager and report builder;

in above situation, if I added "user1" and gave role such as "Browser" role to "user1" in "Permissions" property, if I checked "Secure individual model items independently for this model" in "Model Item Security" property, even I did NOT grant "user1" to root model and any entities under the model, the "user1" is able to access the model and all entities in report builder.

My question is on the same report model, how to set "AdminFilter" (empty security filter) for administrator permissions and set "GeneralFilter" (filtered on UserID) for general user based on their UserID?

I posted my issue on http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1905805&SiteID=17, I attach my post here:

"

I read the following on article http://msdn2.microsoft.com/en-us/library/ms365343.aspx:

"Security filters are always applied, even for users who have Content Manager or Administrator permissions to the model. To allow administrators or other users to see all rows of an entity on which row-level security is defined, you can create an empty security filter (which always returns True) and then use the filter to grant those users access to all the rows."

So I defined 2 filters "GeneralFilter" and "AdminFilter" for "Staff" entity for my report model "SSRSModel", I expect after I deployed the report model, the administrator users use report builder to build reports with all rows available, and the non-admin users can only see rows based on their UserID.

I can only get one result at a time but not both:

either the rows are filtered or not filtered at all, no matter how I set the "SecurityFilter" for the entity: I tried setting both "AdminFilter" and "GeneralFilter" for SecurityFilter at the same time, combination of "DefaultSecurityFilter" and "SecurityFilter", or one at a time.

Anybody please please help me? Thank you!

"

Report server is using Custom Authentication.

Thank you for your help.

Temple1

Filegroups, CPU, & RAID

Not sure if this is the right forum...
I have a table that has its own filegroup on a dedicated RAID 10 array.
Somewhere in Books Online I read that you should create a file in the
filegroup for each CPU in the server. My understanding is that in SQL 2000,
this triggered the server to spawn multiple threads to handle the I/O. My
questions are:
1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
2 - If not, being on a RAID 10 array, does it help in any way to have
multiple files in a group?
Thanks,
-- Brian"Brian Shelden" <BrianShelden@.discussions.microsoft.com> wrote in message
news:EEB24CCA-E759-45EE-8514-5BF3C996B5BC@.microsoft.com...
> Not sure if this is the right forum...
> I have a table that has its own filegroup on a dedicated RAID 10 array.
> Somewhere in Books Online I read that you should create a file in the
> filegroup for each CPU in the server. My understanding is that in SQL
> 2000,
> this triggered the server to spawn multiple threads to handle the I/O. My
> questions are:
> 1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
>
It doesn't matter. You don't want multiple threads for IO hitting a single
volume. One thread is quite enough to saturate your IO channel. A second
thread issuing IO requests over the channel isn't going to improve
performance.
> 2 - If not, being on a RAID 10 array, does it help in any way to have
> multiple files in a group?
>
Multiple files per filegroup and RAID striping both accomplish the same
thing, and there is no need to use both. Both are mechanisms to spread IO
operations targeting a single table or index over a number of spindles.
David|||The "filegroup/file per CPU" rule went out with SQL 7.0. SQL 2000 will spin
up more IO threads as necessary until the controller is saturated. Multiple
files may make it easier to manage moving files due to later growth but will
give zero performance increase if the files are on the same underlying RAID
container. I personally prefer to keep files sizes doen to 100GB or smaller
on all but the largest systems.
There are some "File/CPU" considerations for tempdb for both SQL 2000 and
SQL 2005, but those apply on only the highest volume systems.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Brian Shelden" <BrianShelden@.discussions.microsoft.com> wrote in message
news:EEB24CCA-E759-45EE-8514-5BF3C996B5BC@.microsoft.com...
> Not sure if this is the right forum...
> I have a table that has its own filegroup on a dedicated RAID 10 array.
> Somewhere in Books Online I read that you should create a file in the
> filegroup for each CPU in the server. My understanding is that in SQL
> 2000,
> this triggered the server to spawn multiple threads to handle the I/O. My
> questions are:
> 1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
> 2 - If not, being on a RAID 10 array, does it help in any way to have
> multiple files in a group?
> Thanks,
> -- Brian
>sql

Filegroups, CPU, & RAID

Not sure if this is the right forum...
I have a table that has its own filegroup on a dedicated RAID 10 array.
Somewhere in Books Online I read that you should create a file in the
filegroup for each CPU in the server. My understanding is that in SQL 2000,
this triggered the server to spawn multiple threads to handle the I/O. My
questions are:
1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
2 - If not, being on a RAID 10 array, does it help in any way to have
multiple files in a group?
Thanks,
-- Brian"Brian Shelden" <BrianShelden@.discussions.microsoft.com> wrote in message
news:EEB24CCA-E759-45EE-8514-5BF3C996B5BC@.microsoft.com...
> Not sure if this is the right forum...
> I have a table that has its own filegroup on a dedicated RAID 10 array.
> Somewhere in Books Online I read that you should create a file in the
> filegroup for each CPU in the server. My understanding is that in SQL
> 2000,
> this triggered the server to spawn multiple threads to handle the I/O. My
> questions are:
> 1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
>
It doesn't matter. You don't want multiple threads for IO hitting a single
volume. One thread is quite enough to saturate your IO channel. A second
thread issuing IO requests over the channel isn't going to improve
performance.

> 2 - If not, being on a RAID 10 array, does it help in any way to have
> multiple files in a group?
>
Multiple files per filegroup and RAID striping both accomplish the same
thing, and there is no need to use both. Both are mechanisms to spread IO
operations targeting a single table or index over a number of spindles.
David|||The "filegroup/file per CPU" rule went out with SQL 7.0. SQL 2000 will spin
up more IO threads as necessary until the controller is saturated. Multiple
files may make it easier to manage moving files due to later growth but will
give zero performance increase if the files are on the same underlying RAID
container. I personally prefer to keep files sizes doen to 100GB or smaller
on all but the largest systems.
There are some "File/CPU" considerations for tempdb for both SQL 2000 and
SQL 2005, but those apply on only the highest volume systems.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Brian Shelden" <BrianShelden@.discussions.microsoft.com> wrote in message
news:EEB24CCA-E759-45EE-8514-5BF3C996B5BC@.microsoft.com...
> Not sure if this is the right forum...
> I have a table that has its own filegroup on a dedicated RAID 10 array.
> Somewhere in Books Online I read that you should create a file in the
> filegroup for each CPU in the server. My understanding is that in SQL
> 2000,
> this triggered the server to spawn multiple threads to handle the I/O. My
> questions are:
> 1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
> 2 - If not, being on a RAID 10 array, does it help in any way to have
> multiple files in a group?
> Thanks,
> -- Brian
>

Filegroup recommendation

Are there any general recommendations concerning filegroups? My personal point of view is to place large tables in their own filegroups and group smaller, more static, tables in a single filegroup. Is it also good practice to group small and large index in two separate filegroups or should each large index have their own filegroup? Are there any useful links out there concerning filegroups and configuration?http://www.sql-server-performance.com/filegroups.asp