Item vs. System Level Security

I am trying to deploy RS in a corporate environment. I have folders set up at
different organizational levels. I would like to allow certain users only
within those organization levels to create linked reports and deploy them
within their respective organizational levels. I thought that by granting
those users full item-level security they could achieve this. Unfortunately,
when I tried this it did not work.
1. Which task allows users to view report properties and create linked
reports? I'm assuming this is a system-level task.
2. Is there any way that I can assign this task to a role which restricts
the user to only exercising that authority only within a certain folder. I
obviously don't want to give the user the ability to create linked reports
site wide but only in the respective organizational folder.
3. Is there anyway to combine system-level and item-level tasks into a
single role or to customize security configurations?
Any help would be greatly appreciated.
Thanks,
TedCreating linked reports is an item level task. In report manager go to the
site settings tab and then the 'Configure item-level roledefinitions' link.
From here you can create roles as you see fit. One of the tasks you can
assign is 'Create Linked reports'. You can not combine system and
item-level task, but then again you shouldn't need to.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ted" <Ted@.discussions.microsoft.com> wrote in message
news:287C4AA6-65EC-411A-9E93-4C1CDA463AE2@.microsoft.com...
> I am trying to deploy RS in a corporate environment. I have folders set up
at
> different organizational levels. I would like to allow certain users only
> within those organization levels to create linked reports and deploy them
> within their respective organizational levels. I thought that by granting
> those users full item-level security they could achieve this.
Unfortunately,
> when I tried this it did not work.
> 1. Which task allows users to view report properties and create linked
> reports? I'm assuming this is a system-level task.
> 2. Is there any way that I can assign this task to a role which restricts
> the user to only exercising that authority only within a certain folder. I
> obviously don't want to give the user the ability to create linked reports
> site wide but only in the respective organizational folder.
> 3. Is there anyway to combine system-level and item-level tasks into a
> single role or to customize security configurations?
> Any help would be greatly appreciated.
> Thanks,
> Ted

Issues passing Table Name as a parameter to a Stored Procedure

Hi Friends,

I use the stored procedure pr_Set_Min_Max_Ind to set some indicators in certain type of tables in my databases.

CREATE Procedure [dbo].[pr_Set_Min_Max_Ind] ( @.t SysName, @.k1 SysName, @.k2 SysName, @.r1 SysName, @.r2 SysName )
................
................
...
...
...
................
End

The procedure pr_Set_Min_Max_Ind takes five parameters ( @.t, @.k1, @.k2, @.r1, @.r2 ) of which
@.t is the table name in which I want to set some indicators and
the rest of the parameters (@.k1, @.k2, @.r1, @.r2) are column names in the table @.t

Say, I have this procedure stored in database A. Then to execute the procedure on table TestTable_A in database A , I write the statement as:

exec pr_Set_Min_Max_Ind TestTable_A, Column1, Column2, Column3, Column4 (statement-1)

Now, if I want to execute this procedure on a table TestTable_B in a different database B, I am NOT able to issue a statement like:

exec pr_Set_Min_Max_Ind B.dbo.TestTable_B, Column1, Column2, Column3, Column4 (statement-2)

The only way I now know to accomplish this is to save a copy of this procedure in database B and write a statement similar to statement-1. But, for some reasons I DO NOT want to do that. I just want to have the procedure stored in only one database and be able to pass the table names from other databases in the format database_name.dbo.table_name ;So, guys please let me know how I can pass the table name in the format shown as in statement-2 above.

Thanks in advance for taking time to give me a solution.

With Best Regards,

-Ram.

Depends on how you create your SQL statements based on the table names and column names that you pass to your stored procedure but you should be able to do a SELECT statement from another database in the same SQL Server. Try posting your SP so that we will be able to see where the problem could be.

SQL Server Helper
http://www.sql-server-helper.com

|||

Hi,

The following is the SP that I was referring to.

Thank you.

--

CREATE Procedure [dbo].[pr_Set_Min_Max_Ind](@.t SysName, @.k1 SysName, @.k2 SysName, @.r1 SysName, @.r2 SysName)

As

Declare @.SQL Varchar(8000),

@.x SysName

Set @.x = '##_' + @.t + '_' + Replace(Convert(Char,GetDate(),114),':','')

Begin

-- Tables which have a Min_Ind column

IF ((Select Count(*) From Information_Schema.Columns Where Table_Name = @.t And Column_Name = 'Min_Ind') <> 0)

Begin

-- Rows where the very first column does not have Min_Ind set to Y

Set @.SQL = 'Select * Into ' + @.x + ' From ( Select ' + @.k1 + ' k1,' + @.k2 + ' k2,' + @.r1 + ' r1,' + @.r2 + ' r2, Case When Rank() Over (Partition By ' + @.k1 + ',' + @.k2 + ' Order By ' + @.r1 + ',' + @.r2 + ') = 1 Then ''Y'' Else ''N'' End New_Min_Ind, IsNull(Min_Ind,''-'') Old_Min_Ind From ' + @.t +') t Where Old_Min_Ind <> New_Min_Ind And New_Min_Ind = ''Y'' '

Print @.Sql

Exec(@.SQL)

-- Update any record based on key fields and set the Min_Ind to N if it is not N

Set @.SQL = 'Update a Set Min_Ind = ''N'' From ' + @.x + ' b, ' + @.t + ' a Where b.k1 = a.' + @.k1 + ' And b.k2 = a.' + @.k2 + ' And IsNull(Min_Ind,''-'') <> ''N'' '

Print @.Sql

Exec(@.SQL)

-- Update table and set the Min_Ind to Y based on key and rank fields

Set @.SQL = 'Update a Set Min_Ind = ''Y'' From ' + @.x + ' b, ' + @.t + ' a Where b.k1 = a.' + @.k1 +' And b.k2 = a.' + @.k2 + ' And b.r1 = a.' + @.r1 + ' And b.r2 = a.' + @.r2

Print @.Sql

Exec(@.SQL)

Exec('Drop Table ' + @.x)

End

-- Tables which have a Max_Ind column

IF ((Select Count(*) From Information_Schema.Columns Where Table_Name = @.t And Column_Name = 'Max_Ind') <> 0)

Begin

-- Rows where the very first column does not have Min_Ind set to Y

Set @.SQL = 'Select * Into ' + @.x + ' From ( Select ' + @.k1 + ' k1,' + @.k2 + ' k2,' + @.r1 + ' r1,' + @.r2 +' r2, Case When Rank() Over (Partition By ' + @.k1 + ',' + @.k2 + ' Order By ' + @.r1 + ' Desc,' + @.r2 + ' Desc) = 1 Then ''Y'' Else ''N'' End New_Max_Ind, IsNull(Max_Ind,''-'') Old_Max_Ind From ' + @.t +') t Where Old_Max_Ind <> New_Max_Ind And New_Max_Ind = ''Y'' '

Print @.sql

Exec(@.SQL)

-- Update any record based on key fields and set the Max_Ind to N if it is not N

Set @.SQL = 'Update a Set Max_Ind = ''N'' From ' + @.x + ' b, ' + @.t + ' a Where b.k1 = a.' + @.k1 + ' And b.k2 = a.' + @.k2 + ' And IsNull(Max_Ind,''-'') <> ''N'''

Print @.sql

Exec(@.SQL)

-- Update table and set the Max_Ind to Y based on key and rank fields

Set @.SQL = 'Update a Set Max_Ind = ''Y'' From ' + @.x + ' b, ' + @.t + ' a Where b.k1 = ' + @.k1 +' And b.k2 = ' + @.k2 + ' And b.r1 = ' + @.r1 + ' And b.r2 = ' + @.r2

Print @.sql

Exec(@.SQL)

Exec('Drop Table ' + @.x)

End

Return @.@.Error

End

|||

You will need to prefix your If ... from Information_Schema.Columns with the name of the database passed from your @.t variable...

Something like this

declare @.DBName varchar(50)
declare @.TblName varhcar(50)

Set @.DBName = parsename(@.t, 3)
Set @.TblName = parsename(@.t, 1)

-- 1=object name, 2=schema name, 3=database name, 4= server name

Then you'll need to change your If count(*)... from Information_Schema.Columns statement into dynamic SQL (so that the database name gets interpretted)...

I would use sp_executesql to return your count (from the If statement against information_schema). See BOL on how to return a value using sp_executesql.

Note, just a partial example...

SET @.SQLString = N'Select @.retCountOut = Count(*) From ' + @.dbname + '.Information_Schema.Columns Where Table_Name = ''' + @.tblname + ''' Column_Name = 'Min_Ind''

SET @.ParamDef = N'@.retCountOut int Output, @.dbname varchar(50), @.tblname varchar(50)';

EXECUTE sp_executesql @.SQLString, @.ParamDef, @.dbname = @.dbname, @.tblname = @.tblname, @.retCountout=@.retCount OUTPUT;

If @.retCount<> 0
Begin.....

|||I would strongly suggest against this sort of thing. This code is really hard to follow and will be problematic. I would consider using this code to generate non-dynamic SQL that you run directly. Then the code generator might be dynamic, but each module won't be, and will perform better, and be easier to manage.

Issue with distinct count

I have a table which contains activities, and I am trying to extract a section that has less than a certain number of activities for a given date range. I am receiving an error of

Incorrect syntax near 'activityid' for my select clause. Any help would be greatly appreciated.

Code Snippet

Query I'm working with
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
declare @.sectionid int, @.maxcount int, @.stdate datetime, @.enddate datetime
set @.sectionid = 37
set @.maxcount = 5
set @.stdate = '01/01/06'
set @.enddate = '01/31/06'

select distinct (count activityid) <= @.maxcount, a.sectionid
from activity a
where
a.sectionid = @.sectionid
AND
(a.date between @.stdate and @.enddate)

Table Definition
>>>>>>>>>>>>>>>>>>>>>>>>>>

CREATE PROCEDURE [dbo].[p_Activity_Create]
(
@.ActivityID INT OUTPUT,
@.SectionID INT,
@.ActivityCategoryID INT,
@.STIAssessmentTestID INT,
@.Name VARCHAR(30),
@.Date DATETIME,
@.MaximumScore DECIMAL(6,2),
@.WeightAddition DECIMAL(9,6),
@.WeightMultiplier DECIMAL(9,6),
@.IsAssessment BIT,
@.IsHomework BIT,
@.IsScored BIT,
@.MayBeDropped BIT,
@.IsDropped BIT,
@.IsComplete BIT,
@.Unit VARCHAR(50)

)
AS

SET NOCOUNT ON

INSERT INTO [dbo].[Activity]
(SectionID,
ActivityCategoryID,
STIAssessmentTestID,
[Name],
[Date],
MaximumScore,
WeightAddition,
WeightMultiplier,
IsAssessment,
IsHomework,
IsScored,
MayBeDropped,
IsDropped,
IsComplete,
Unit)
VALUES
(@.SectionID,
@.ActivityCategoryID,
@.STIAssessmentTestID,
@.Name,
@.Date,
@.MaximumScore,
@.WeightAddition,
@.WeightMultiplier,
@.IsAssessment,
@.IsHomework,
@.IsScored,
@.MayBeDropped,
@.IsDropped,
@.IsComplete,
@.Unit)

SET @.ActivityID = SCOPE_IDENTITY()
GO

Table data
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

ActivityID SectionID Name Date ActivityCategoryID STIAssessmentTestID MaximumScore WeightAddition WeightMultiplier IsAssessment IsHomework IsScored MayBeDropped IsDropped IsComplete 11 37 Homework 9/20/2005 49 NULL 25 0 1 0 1 1 1 0 0 14 37 Midterm 10/16/2005 51 NULL 100 0 1 1 0 1 1 0 0 38 37 Homework 1/16 1/16/2006 49 NULL 25 0 1 0 1 1 1 0 0 39 37 Homework 1/17 1/17/2006 49 NULL 25 0 1 0 1 1 1 0 0 40 37 Extra 1/18 1/18/2006 52 NULL 50 0 1 0 0 1 1 0 0 41 37 Homework 1/19 1/19/2006 49 NULL 25 0 1 0 1 1 1 0 0 12 37 Quiz 1/20/2006 50 NULL 50 0 1 0 0 1 1 0 0 13 37 Test 4/20/2006 51 NULL 100 0 1 0 0 1 1 0 0 15 37 Final 5/20/2006 51 NULL 100 0 1 1 0 1 1 0 0 1 42 Homework 9/20/2005 5 NULL 25 0 1 0 1 1 1 0 0 4 42 Midterm 10/16/2005 7 NULL 100 0 1 1 0 1 1 0 0 2 42 Quiz 1/20/2006 6 NULL 50 0 1 0 0 1 1 0 0 3 42 Test 4/20/2006 7 NULL 100 0 1 0 0 1 1 0 0 5 42 Final 5/20/2006 7 NULL 100 0 1 1 0 1 1 0 0 26 43 Homework 9/20/2005 85 NULL 25 0 1 0 1 1 1 0 0 29 43 Midterm 10/16/2005 87 NULL 100 0 1 1 0 1 1 0 0 27 43 Quiz 1/20/2006 86 NULL 50 0 1 0 0 1 1 0 0 42 43 Homework 4/17 4/17/2006 85 NULL 25 0 1 0 1 1 1 0 0 43 43 Extra 4/18 4/18/2006 88 NULL 50 0 1 0 0 1 1 0 0 44 43 Homework 4/19 4/19/2006 85 NULL 25 0 1 0 1 1 1 0 0 28 43 Test 4/20/2006 87 NULL 100 0 1 0 0 1 1 0 0 45 43 Extra 4/21 4/21/2006 88 NULL 50 0 1 0 0 1 1 0 0 30 43 Final 5/20/2006 87 NULL 100 0 1 1 0 1 1 0 0 16 49 Homework 9/20/2005 61 NULL 25 0 1 0 1 1 1 0 0 19 49 Midterm 10/16/2005 63 NULL 100 0 1 1 0 1 1 0 0 17 49 Quiz 1/20/2006 62 NULL 50 0 1 0 0 1 1 0 0 18 49 Test 4/20/2006 63 NULL 100 0 1 0 0 1 1 0 0 20 49 Final 5/20/2006 63 NULL 100 0 1 1 0 1 1 0 0 21 50 Homework 9/20/2005 65 NULL 25 0 1 0 1 1 1 0 0 24 50 Midterm 10/16/2005 67 NULL 100 0 1 1 0 1 1 0 0 22 50 Quiz 1/20/2006 66 NULL 50 0 1 0 0 1 1 0 0 23 50 Test 4/20/2006 67 NULL 100 0 1 0 0 1 1 0 0 25 50 Final 5/20/2006 67 NULL 100 0 1 1 0 1 1 0 0 31 53 Homework 9/20/2005 121 NULL 25 0 1 0 1 1 1 0 0 34 53 Midterm 10/16/2005 123 NULL 100 0 1 1 0 1 1 0 0 32 53 Quiz 1/20/2006 122 NULL 50 0 1 0 0 1 1 0 0 33 53 Test 4/20/2006 123 NULL 100 0 1 0 0 1 1 0 0 35 53 Final 5/20/2006 123 NULL 100 0 1 1 0 1 1 0 0 6 67 Homework 9/20/2005 33 NULL 25 0 1 0 1 1 1 0 0 9 67 Midterm 10/16/2005 35 NULL 100 0 1 1 0 1 1 0 0 7 67 Quiz 1/20/2006 34 NULL 50 0 1 0 0 1 1 0 0 8 67 Test 4/20/2006 35 NULL 100 0 1 0 0 1 1 0 0 10 67 Final 5/20/2006 35 NULL 100 0 1 1 0 1 1 0 0

43 row(s) affected)

The problem is with the line:

select distinct (count activityid) <= @.maxcount, a.sectionid

To me this looks like pseudocode.

You might try something more like:

select sectionId,
count(distinct activityId)
from activity a
where a.sectionId = @.sectionId
and a.date between @.stDate and @.endDate
group by sectionId
having count(distinct activityId) <= @.maxCount