Row and Cell Segurity

I am trying to implement row-security in SQL 2005 but i make a query to make a view

CREATE VIEW vwVisibleLabels
AS

SELECT ID, Label.ToString()
FROM tblUniqueLabel WITH (NOLOCK)
WHERE
ID IN --Classification
(SELECT ID FROM tblUniqueLabelMarking WITH (NOLOCK)
WHERE CategoryID = 1 AND IS_MEMBER(MarkingRoleName) = 1)
AND --Compartments
1 = ALL(SELECT IS_MEMBER(MarkingRoleName) FROM tblUniqueLabelMarking
WHERE CategoryID = 2 AND UniqueLabelID = tblUniqueLabel.ID)
GO

And the error is

Msg 208, Level 16, State 1, Procedure vwVisibleLabels, Line 4
Invalid object name 'tblUniqueLabel'.

The tblUniquelabel does exist, what is going on?
Please someone help me!!!!

PS. I am following the white papper on Implementing Row and Cell Level Security from the microsoft site.

Could you please post a script that reproes this problem? I don't see anything wrong with the view definition unless you have a typo somewhere or wrong case (matters in case-sensitive collation).|||

I think the problem is in the ToString(). I have the colum label with the data type nchar(20) do you think that could be the problem?

|||It is hard to tell anything conclusive without seeing a repro of the problem. So please post back with sample schema/script.|||

But what do you mean by script scheam? Is it the code that generates the database? Here is it?

Thanks

Ps. I made a view only ti list the tbUniquelabel and it was fine so the problem isn't the table not existing or being badly written.

|||Not the entire database script but just the SQL statements that can repro the problem. It needn't be the actual schema also.|||

When you run the code

SELECT ID, Label
FROM dbo.tblUniqueLabel WITH (NOLOCK)
WHERE (ID IN
(SELECT dbo.tblUniqueLabel.ID
FROM dbo.tblUniqueLabelMarking WITH (NOLOCK)
WHERE (CategoryID = 1) AND (IS_MEMBER(MarkingRoleName) = 1)))

No error but no right result because, i think that IS_MEMBER(MarkingRoleName) = 1 always is 0. so every time it gives me a empty Table with the label and id colum

So i need to convert Label to string so it can be compared in the IS_MEMBER(MarkingRoleName) = 1

But if i run

SELECT ID, Label.ToString() AS Expr1
FROM dbo.tblUniqueLabel WITH (NOLOCK)
WHERE (ID IN
(SELECT dbo.tblUniqueLabel.ID
FROM dbo.tblUniqueLabelMarking WITH (NOLOCK)
WHERE (CategoryID = 1) AND (IS_MEMBER(MarkingRoleName) = 1)))

It gives me the folowing messege:

Cannot find the colum label (which is impossible because the first code runs), or the user-defined function or aggregate "Label.ToString", or the name is ambiguous.

When i run

select schema_name(schema_id) from sys.objects where name = 'tblUniqueLabel' the is result is a table with one row and one column with the heading expr 1 and the cell with dbo written on on

Thanks

|||Is the label column a CLR UDT? If so, does it have a ToString() method? If it is a SQL data type then use CAST to convert the value to string.|||

Looking back to the code it does not seem like you need to convert anything. It was just what i thought the toString() procedure did.

I put nchar(20 ) on every column is that the mistake?

How do i chandge it to the CLR type and what is it?

Thanks again.

|||

Why don't you use CAST or CONVERT?

Stjepan

|||dude, you probably have tblUniqueLabel sitting in a different schema, or your default schema isn't dbo.

reference tblUniqueLabel using the form SchemaName.tblUniqueLabel

Also, you don't have to do .ToString() - there's no such function in sql server. just reference label as is.