Determine Report Permissions with T-SQL

Is there a way, using the system tables in the ReportServer or
ReportServerTempDB to determine which Active Directory groups have access to
which reports. I want to document what I would otherwise have to go
report-by-report in the Report Manager screen to see. I've looked at
several of the system tables (Users, DataSource, Policies, Roles,
ConfigurationInfo, Catalog) but have had no luck.You need to use the GetPolicies and SetPolicies methods on the web service.
You don't want to do anything directly to the database.
Here's a code snippet giving you an idea of how to use these methods:
private bool AddUserToFolderPolicy(string folder, string user, ref string
errMessage)
{
try
{
//Get the Browser role
Role[]roles = m_ReportingService.ListRoles();
Role browserRole = new Role();
foreach (Role r in roles)
{
if (r.Name == "Browser") browserRole = r;
break;
}
Role[] policyRoles = new Role[1];
policyRoles[0] = new Role();
policyRoles[0] =browserRole;
//Get the current policies of the folder in question
string path = "/" + folder;
bool inheritParent = false;
Policy[] currentPolicies = m_ReportingService.GetPolicies(path, out
inheritParent);
//If the user is currently in the current policy set just return
for(int i=0;i<currentPolicies.Length;i++)
if(currentPolicies[i].GroupUserName == user)
return true;
//Create the new policy array and add the new user
ArrayList arrPolicies = new ArrayList(currentPolicies);
Policy p = new Policy();
p.GroupUserName = user;
p.Roles = policyRoles;
arrPolicies.Add(p);
Policy[] finalPolicies = (Policy[])arrPolicies.ToArray(typeof(Policy));
//Set the policies
m_ReportingService.SetPolicies(path,finalPolicies);
}
catch (Exception e)
{
errMessage = e.Message;
return false;
}
return true;
}
Adrian M.
MCP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:B4B65C16-C9C8-4872-85D9-C75BADC0F53D@.microsoft.com...
> Is there a way, using the system tables in the ReportServer or
> ReportServerTempDB to determine which Active Directory groups have access
> to
> which reports. I want to document what I would otherwise have to go
> report-by-report in the Report Manager screen to see. I've looked at
> several of the system tables (Users, DataSource, Policies, Roles,
> ConfigurationInfo, Catalog) but have had no luck.
>|||Adrian, thank you for the prompt reply. Unfortunately, I am not a C#
developer and need to accomplish this task in T-SQL. I don't really want to
"do" anything to the tables, I just want to "get" something from them, just
as I would a system table in master or anywhere else in SQL Server. Does
anyone ([MSFT] people perhaps?) know if this is possible or if there is any
documentation on how to navigate these tables?
"Adrian M." wrote:
> You need to use the GetPolicies and SetPolicies methods on the web service.
> You don't want to do anything directly to the database.
> Here's a code snippet giving you an idea of how to use these methods:
> private bool AddUserToFolderPolicy(string folder, string user, ref string
> errMessage)
> {
> try
> {
> //Get the Browser role
> Role[]roles = m_ReportingService.ListRoles();
> Role browserRole = new Role();
> foreach (Role r in roles)
> {
> if (r.Name == "Browser") browserRole = r;
> break;
> }
> Role[] policyRoles = new Role[1];
> policyRoles[0] = new Role();
> policyRoles[0] =browserRole;
> //Get the current policies of the folder in question
> string path = "/" + folder;
> bool inheritParent = false;
> Policy[] currentPolicies = m_ReportingService.GetPolicies(path, out
> inheritParent);
> //If the user is currently in the current policy set just return
> for(int i=0;i<currentPolicies.Length;i++)
> if(currentPolicies[i].GroupUserName == user)
> return true;
> //Create the new policy array and add the new user
> ArrayList arrPolicies = new ArrayList(currentPolicies);
> Policy p = new Policy();
> p.GroupUserName = user;
> p.Roles = policyRoles;
> arrPolicies.Add(p);
> Policy[] finalPolicies = (Policy[])arrPolicies.ToArray(typeof(Policy));
> //Set the policies
> m_ReportingService.SetPolicies(path,finalPolicies);
> }
> catch (Exception e)
> {
> errMessage = e.Message;
> return false;
> }
> return true;
> }
>
> --
> Adrian M.
> MCP
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:B4B65C16-C9C8-4872-85D9-C75BADC0F53D@.microsoft.com...
> > Is there a way, using the system tables in the ReportServer or
> > ReportServerTempDB to determine which Active Directory groups have access
> > to
> > which reports. I want to document what I would otherwise have to go
> > report-by-report in the Report Manager screen to see. I've looked at
> > several of the system tables (Users, DataSource, Policies, Roles,
> > ConfigurationInfo, Catalog) but have had no luck.
> >
>
>|||Microsoft doesn't support directly access to the Report Server DB (including
queries). Supported access is through the tools provided (Report Manager,
web service, etc...)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_dbadmin_v1_4915.asp
--
Adrian M.
MCP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:CDD9B6BC-38D9-400C-B905-D184949D0E91@.microsoft.com...
> Adrian, thank you for the prompt reply. Unfortunately, I am not a C#
> developer and need to accomplish this task in T-SQL. I don't really want
> to
> "do" anything to the tables, I just want to "get" something from them,
> just
> as I would a system table in master or anywhere else in SQL Server. Does
> anyone ([MSFT] people perhaps?) know if this is possible or if there is
> any
> documentation on how to navigate these tables?
> "Adrian M." wrote:
>> You need to use the GetPolicies and SetPolicies methods on the web
>> service.
>> You don't want to do anything directly to the database.
>> Here's a code snippet giving you an idea of how to use these methods:
>> private bool AddUserToFolderPolicy(string folder, string user, ref
>> string
>> errMessage)
>> {
>> try
>> {
>> //Get the Browser role
>> Role[]roles = m_ReportingService.ListRoles();
>> Role browserRole = new Role();
>> foreach (Role r in roles)
>> {
>> if (r.Name == "Browser") browserRole = r;
>> break;
>> }
>> Role[] policyRoles = new Role[1];
>> policyRoles[0] = new Role();
>> policyRoles[0] =browserRole;
>> //Get the current policies of the folder in question
>> string path = "/" + folder;
>> bool inheritParent = false;
>> Policy[] currentPolicies = m_ReportingService.GetPolicies(path, out
>> inheritParent);
>> //If the user is currently in the current policy set just return
>> for(int i=0;i<currentPolicies.Length;i++)
>> if(currentPolicies[i].GroupUserName == user)
>> return true;
>> //Create the new policy array and add the new user
>> ArrayList arrPolicies = new ArrayList(currentPolicies);
>> Policy p = new Policy();
>> p.GroupUserName = user;
>> p.Roles = policyRoles;
>> arrPolicies.Add(p);
>> Policy[] finalPolicies =>> (Policy[])arrPolicies.ToArray(typeof(Policy));
>> //Set the policies
>> m_ReportingService.SetPolicies(path,finalPolicies);
>> }
>> catch (Exception e)
>> {
>> errMessage = e.Message;
>> return false;
>> }
>> return true;
>> }
>>
>> --
>> Adrian M.
>> MCP
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:B4B65C16-C9C8-4872-85D9-C75BADC0F53D@.microsoft.com...
>> > Is there a way, using the system tables in the ReportServer or
>> > ReportServerTempDB to determine which Active Directory groups have
>> > access
>> > to
>> > which reports. I want to document what I would otherwise have to go
>> > report-by-report in the Report Manager screen to see. I've looked at
>> > several of the system tables (Users, DataSource, Policies, Roles,
>> > ConfigurationInfo, Catalog) but have had no luck.
>> >
>>|||Even if we were to document the tables, there is no way to use TSQL to get
this (without some extended SPs). We use Windows APIs to resolve group
membership and determine effective permissions from the ACL we store in the
database.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:CDD9B6BC-38D9-400C-B905-D184949D0E91@.microsoft.com...
> Adrian, thank you for the prompt reply. Unfortunately, I am not a C#
> developer and need to accomplish this task in T-SQL. I don't really want
> to
> "do" anything to the tables, I just want to "get" something from them,
> just
> as I would a system table in master or anywhere else in SQL Server. Does
> anyone ([MSFT] people perhaps?) know if this is possible or if there is
> any
> documentation on how to navigate these tables?
> "Adrian M." wrote:
>> You need to use the GetPolicies and SetPolicies methods on the web
>> service.
>> You don't want to do anything directly to the database.
>> Here's a code snippet giving you an idea of how to use these methods:
>> private bool AddUserToFolderPolicy(string folder, string user, ref
>> string
>> errMessage)
>> {
>> try
>> {
>> //Get the Browser role
>> Role[]roles = m_ReportingService.ListRoles();
>> Role browserRole = new Role();
>> foreach (Role r in roles)
>> {
>> if (r.Name == "Browser") browserRole = r;
>> break;
>> }
>> Role[] policyRoles = new Role[1];
>> policyRoles[0] = new Role();
>> policyRoles[0] =browserRole;
>> //Get the current policies of the folder in question
>> string path = "/" + folder;
>> bool inheritParent = false;
>> Policy[] currentPolicies = m_ReportingService.GetPolicies(path, out
>> inheritParent);
>> //If the user is currently in the current policy set just return
>> for(int i=0;i<currentPolicies.Length;i++)
>> if(currentPolicies[i].GroupUserName == user)
>> return true;
>> //Create the new policy array and add the new user
>> ArrayList arrPolicies = new ArrayList(currentPolicies);
>> Policy p = new Policy();
>> p.GroupUserName = user;
>> p.Roles = policyRoles;
>> arrPolicies.Add(p);
>> Policy[] finalPolicies =>> (Policy[])arrPolicies.ToArray(typeof(Policy));
>> //Set the policies
>> m_ReportingService.SetPolicies(path,finalPolicies);
>> }
>> catch (Exception e)
>> {
>> errMessage = e.Message;
>> return false;
>> }
>> return true;
>> }
>>
>> --
>> Adrian M.
>> MCP
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:B4B65C16-C9C8-4872-85D9-C75BADC0F53D@.microsoft.com...
>> > Is there a way, using the system tables in the ReportServer or
>> > ReportServerTempDB to determine which Active Directory groups have
>> > access
>> > to
>> > which reports. I want to document what I would otherwise have to go
>> > report-by-report in the Report Manager screen to see. I've looked at
>> > several of the system tables (Users, DataSource, Policies, Roles,
>> > ConfigurationInfo, Catalog) but have had no luck.
>> >
>>

detect existence of datetime fields

I'm looking for an efficient t-sql script to loop through all user
tables in a db and determine/print the value of each row having a
datetime field (including cases where there are multiple datetime
fields per row)

Help greatly appreciated.."JTWhaler" <jonahturnedwhaler@.yahoo.com> wrote in message
news:ed564348.0401022017.34730d8@.posting.google.co m...
> I'm looking for an efficient t-sql script to loop through all user
> tables in a db and determine/print the value of each row having a
> datetime field (including cases where there are multiple datetime
> fields per row)
> Help greatly appreciated..

Here is a script that does a reasonable task of generating
a useful data dictionary out of any database.

To identify the datetime fields simply add
a restriction line in the WHERE bit ... such as
and t.name in ('DateTime', 'SmallDateTime')

select substring(o.name,1,50) as "Table Name",
o.type "Typ",
c.colid,
substring(c.name,1,30) as "Column Name",
substring(t.name,1,30) as "DataType",
c.length

from sysobjects o
left join syscolumns c on (o.id=c.id)
left join systypes t on (c.xusertype=t.xusertype)
where o.type = 'U'
order by 1,3|||jonahturnedwhaler@.yahoo.com (JTWhaler) wrote in message news:<ed564348.0401022017.34730d8@.posting.google.com>...
> I'm looking for an efficient t-sql script to loop through all user
> tables in a db and determine/print the value of each row having a
> datetime field (including cases where there are multiple datetime
> fields per row)
> Help greatly appreciated..

Hi Jonah

The attached T-sql will create a database table that contains an entry
for each datetime column in the database

drop table datetime_column

create table datetime_column
(
table_name varchar(200)
,column_name varchar(200)
,datetime_value datetime null
)
declare @.table_schema as varchar(200)
declare @.table_name as varchar(200)
declare @.column_name as varchar(200)
declare @.query as nvarchar(1000)

DECLARE column_cursor CURSOR FOR
SELECT
table_name, column_name
FROM
INFORMATION_Schema.columns
where
data_type = 'datetime'
and table_name != 'datetime_column'

open column_cursor

FETCH NEXT FROM
column_cursor
INTO
@.table_name, @.column_name

while @.@.fetch_status = 0
begin
select @.query = ' insert into datetime_column select ' + '''' +
@.table_name + '''' + ',' + '''' + @.column_name + '''' + ',' +
@.column_name + ' from ' + @.table_name
--select @.query
exec sp_executesql @.query

FETCH NEXT FROM
column_cursor
INTO
@.table_name, @.column_name
end

close column_cursor
deallocate column_cursor
select * from datetime_column

Design T-SQL WHERE

Hi ,

I have following statement :

SELECT * FROM Table WHERE Col1>=@.Col1L AND Col1<=@.Col1H AND Col2>=@.Col2L AND Col2<=@.Col2LH AND ... AND ColN>=@.ColNL AND ColN<=@.ColNH

But sometimes variables e.g. @.Col1L and @.Col1H may cover whole range of available values so they will be there for nothing
E.g. It may happen my query will be sufficient if I will have

SELECT * FROM Table WHERE Col1>=@.Col1L AND Col1<=@.Col1H -- and no other columns, because @.Col2L will be lowest possible assignable value and @.Col2H highest possible value, etc.

How should I design this type of query ?

Should I dynamically create WHERE clause e.g.:

IF @.Col2L<>@.MinPossibleValue OR @.Col2H<>@.MaxPossibleValue
SET @.WHERE=@.WHERE + 'Col2>=' + @.Col2L + ' AND Col2<=' + @.Col2LH
...
EXEC(@.Query)

Got any other suggestion for designing query ? Or improving performance ...

Thank you for your opinion.You could use dynamic SQL but it is not worth the effort and complexity to do that. It also depends on the indexes and your search conditions. Below are some links that you should check out:

Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html

The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

Design Question - SQL 2000 to 2005 App Upgrade (CLR?)

I'm upgrading an app I wrote from SQL 2000 to 2005 looking for some
high-level design advice, just to determine how much should be T-SQL vs.
CLR, and the best approach to take for performance. For background, I'm a
strong c# developer, but with only average SQL skills and I'm just getting
into SQL 2005.
My Current (SQL 2000) Setup:
--
My C# app regularly collects performance information, then inserts it into a
table. On insert in that table I have a trigger to kick off a couple stored
procs that compare the new data to other tables where thresholds are set.
As a simple example, it essentially does a "SELECT * FROM Thresholds WHERE
@.NewlyInsertedValue > ThresholdValue" to determine if the current value is
in violation of any thresholds. (There are a couple more columns that are
considered that makes the query a little more intensive, but this is the
general idea)
If thresholds are violated, it inserts a record into an 'alerts' table and
generates an email (by another stored proc). If no thresholds are violated,
it runs a stored proc to clear any alerts that might have been created
previously since we're not in violation anymore. Since this happens EVERY
time a new value is collected, I really want to optimize this as much as
possible.
My Desired (SQL 2005) Setup:
--
I'm going to replace certain obvious functions like the "send email"
function from old SQL objects to net CLR objects. However, I want to
further optimize the system that compares incoming data with thresholds and
generates alerts as necessary. It seems very wasteful to do selects every
time a new value is inserted, and as the tables grow even with proper
indexing it is going to get expensive. Is there a way I can take advantage
of CLR to maybe keep these thresholds in memory to compare them against?
Any other ideas about how to accomplish this most efficiently?
Craig
PS - I can post DDL if you really want it, I was just trying to get high
level design ideas from someone that's done a lot of SQL CLR work already.For general data manipulation especially Inserts, Updates, Deletes and
normal Selects TSQL will always be the best choice. The CLR is useful for
heavy calculations / aggregations or when you simply can't do something with
TSQL. Of coarse anytime you need to visit the filesystem or need the
equivalent of an extended sp the CLR is prime for that as well. AS for the
mail component you can use the new DatabaseMail which works pretty well and
a lot less troublesome than the MAPI version in 2000. But in general I
suggest you use stored procedures for this instead of triggers. You have a
fair amount of processing to do and you should keep triggers as trim as
possible. I don't see any reason this logic can not be put into a standard
TSQL stored procedure. But as for checking the values why not do that in the
front end or middle tier before you even attempt the insert? That kind of
information is pretty much static and can be cached very easily in the
middle tier or front end. If these inserts are many you can save a lot of
processing etc. on the back end by validating the data before you send it
in.
Andrew J. Kelly SQL MVP
"Craig S" <cscheets@.dontspam.kc.rr.com> wrote in message
news:OIG$CfXDGHA.1544@.TK2MSFTNGP10.phx.gbl...
> I'm upgrading an app I wrote from SQL 2000 to 2005 looking for some
> high-level design advice, just to determine how much should be T-SQL vs.
> CLR, and the best approach to take for performance. For background, I'm a
> strong c# developer, but with only average SQL skills and I'm just getting
> into SQL 2005.
> My Current (SQL 2000) Setup:
> --
> My C# app regularly collects performance information, then inserts it into
> a table. On insert in that table I have a trigger to kick off a couple
> stored procs that compare the new data to other tables where thresholds
> are set. As a simple example, it essentially does a "SELECT * FROM
> Thresholds WHERE @.NewlyInsertedValue > ThresholdValue" to determine if the
> current value is in violation of any thresholds. (There are a couple more
> columns that are considered that makes the query a little more intensive,
> but this is the general idea)
> If thresholds are violated, it inserts a record into an 'alerts' table and
> generates an email (by another stored proc). If no thresholds are
> violated, it runs a stored proc to clear any alerts that might have been
> created previously since we're not in violation anymore. Since this
> happens EVERY time a new value is collected, I really want to optimize
> this as much as possible.
> My Desired (SQL 2005) Setup:
> --
> I'm going to replace certain obvious functions like the "send email"
> function from old SQL objects to net CLR objects. However, I want to
> further optimize the system that compares incoming data with thresholds
> and generates alerts as necessary. It seems very wasteful to do selects
> every time a new value is inserted, and as the tables grow even with
> proper indexing it is going to get expensive. Is there a way I can take
> advantage of CLR to maybe keep these thresholds in memory to compare them
> against? Any other ideas about how to accomplish this most efficiently?
> Craig
> PS - I can post DDL if you really want it, I was just trying to get high
> level design ideas from someone that's done a lot of SQL CLR work already.
>|||First, you're right - everything I'm doing can be done in T-SQL (It is today
in SQL 2000). I just wanted to see if CLR would offer any optimization.
Secondly, I think you're right about caching the data and performing the
checks in the middle tier for violations (I'm using web services). From
what I understand I can actually subscribe to changes to a table/view in
2005 so that I could cache that info and just rebuild it any time the
thresholds table was updated. The thresholds data is pretty static, so this
could definately work.
I guess this is basically what I was trying to do, but for some reason
thought it needed to live in the db. Logic is always best left in the
middle/front tier anyway I guess.
So, good call Andrew. Anyone else have anything to add/recommend?
Craig
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O9eKD9XDGHA.3444@.TK2MSFTNGP10.phx.gbl...
> For general data manipulation especially Inserts, Updates, Deletes and
> normal Selects TSQL will always be the best choice. The CLR is useful for
> heavy calculations / aggregations or when you simply can't do something
> with TSQL. Of coarse anytime you need to visit the filesystem or need the
> equivalent of an extended sp the CLR is prime for that as well. AS for the
> mail component you can use the new DatabaseMail which works pretty well
> and a lot less troublesome than the MAPI version in 2000. But in general
> I suggest you use stored procedures for this instead of triggers. You have
> a fair amount of processing to do and you should keep triggers as trim as
> possible. I don't see any reason this logic can not be put into a
> standard TSQL stored procedure. But as for checking the values why not do
> that in the front end or middle tier before you even attempt the insert?
> That kind of information is pretty much static and can be cached very
> easily in the middle tier or front end. If these inserts are many you can
> save a lot of processing etc. on the back end by validating the data
> before you send it in.
> --
> Andrew J. Kelly SQL MVP
>
> "Craig S" <cscheets@.dontspam.kc.rr.com> wrote in message
> news:OIG$CfXDGHA.1544@.TK2MSFTNGP10.phx.gbl...
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:574655
Craig S (cscheets@.dontspam.kc.rr.com) writes:
> My C# app regularly collects performance information, then inserts it
> into a table. On insert in that table I have a trigger to kick off a
> couple stored procs that compare the new data to other tables where
> thresholds are set. As a simple example, it essentially does a "SELECT *
> FROM Thresholds WHERE @.NewlyInsertedValue > ThresholdValue" to determine
> if the current value is in violation of any thresholds. (There are a
> couple more columns that are considered that makes the query a little
> more intensive, but this is the general idea)
As long you have full control over the data, using a stored procedure
to insert is better. Triggers are more difficult to write, and there
is one ugly thing about them: they can disappear without notice. If
a stored procedure is dropped inadvertenly, your application will
cry out, but if the trigger is missing, you will only get incorrect
results.

> It seems very wasteful to do selects every time a new value is inserted,
> and as the tables grow even with proper indexing it is going to get
> expensive. Is there a way I can take advantage of CLR to maybe keep
> these thresholds in memory to compare them against?
Maybe there is, but it would be a bad idea. Don't worry, that data
is in memory already. SQL Server takes care of that, and keeps as
much data in cache as possible.

> I guess this is basically what I was trying to do, but for some reason
> thought it needed to live in the db. Logic is always best left in the
> middle/front tier anyway I guess.
Personally, I'm a strong believer in having the business logic where
the database is.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx