ROWNUM In Oracle

Is there any way to implement ROWNUM in Oracle to select row no.
Is there any way to select the row no when retrieving records as
1 Tv
2 Fridge
3 RadioHi,
There is no concept of Rownum in sql server.
But you could write ur own query to get the serial number.
Use the below script as sample:-
create table item(item_code varchar(05))
go
insert into item values('a1')
insert into item values('a2')
insert into item values('a3')
insert into item values('a4')
go
SELECT (SELECT COUNT(i.item_code)
FROM item i
WHERE i.item_code >= o.item_code ) AS RowID,
item_code
FROM item o
ORDER BY RowID
Thanks
Hari
SQL Server MVP
"Renjith" <Renjith@.discussions.microsoft.com> wrote in message
news:3BDAC82C-4AB5-4631-ABAE-AB693B0E1312@.microsoft.com...
> Is there any way to implement ROWNUM in Oracle to select row no.
> Is there any way to select the row no when retrieving records as
> 1 Tv
> 2 Fridge
> 3 Radio|||Hi
There is not a ROWNUM function in SQLServer 2000 using an identity column is
usually the alternative. If you want to rank your values then you could use
a
construct like:
e.g
SELECT ( SELECT COUNT(*) FROM MyTable T WHERE t.id <= M.id ) AS Rank,
col1, col2
FROM MyTable M
These links may also help.
http://vyaskn.tripod.com/ oracle_sq...ent
s.htm
http://www.microsoft.com/resources/...r />
0761.mspx
http://www.microsoft.com/sql/evalua...pare/oracle.asp
John
"Renjith" wrote:

> Is there any way to implement ROWNUM in Oracle to select row no.
> Is there any way to select the row no when retrieving records as
> 1 Tv
> 2 Fridge
> 3 Radio|||Hi
If it is a big table then the count(*) as inner query will create
performance problem '
"John Bell" wrote:
> Hi
> There is not a ROWNUM function in SQLServer 2000 using an identity column
is
> usually the alternative. If you want to rank your values then you could us
e a
> construct like:
> e.g
> SELECT ( SELECT COUNT(*) FROM MyTable T WHERE t.id <= M.id ) AS Rank,
> col1, col2
> FROM MyTable M
> These links may also help.
> http://vyaskn.tripod.com/ oracle_sq...ent
s.htm
> http://www.microsoft.com/resources/.../>
/c0761.mspx
> http://www.microsoft.com/sql/evalua...pare/oracle.asp
> John
> "Renjith" wrote:
>|||Hi
It may, and indexing would reduce the problem.
You can also do something like:
CREATE TABLE MyTest ( id int not null identity(1,1), val char(1))
INSERT INTO MyTest ( val )
SELECT 'A'
UNION ALL SELECT 'B'
UNION ALL SELECT 'C'
UNION ALL SELECT 'D'
UNION ALL SELECT 'E'
DELETE FROM MyTest where val = 'C'
SELECT m.id, COUNT(*) as Rank, m.val
FROM MyTest m
JOIN MyTest r ON R.id <= M.id
GROUP BY m.id, M.val
ORDER BY 2
Another alternative would be do deligate the numbering to the client.
John
"Renjith" wrote:
> Hi
> If it is a big table then the count(*) as inner query will create
> performance problem '
> "John Bell" wrote:
>|||Hi
You may want to look at Itzik Ben-Gan's articles in the May 2005 SQL
Server Magazine.
http://www.windowsitpro.com/Article...5828/45828.html
http://www.windowsitpro.com/Article...2302/42302.html
http://www.windowsitpro.com/Article...2646/42646.html
John

Row locking

Hi,

I am looking for a locking advice.

I am looking to implement a queue like mechanism where in multiple connections / users connect to the database to get a single row. I do it as follows.

I have two tables A & B. I have a stored procedure which joins the two tables and returns only one row whose status flag is N [N - not read before]. The returned row is the oldest whose status is N. The data returned is mainly from table A, and only one column from B. As we return the selected row its status should be update to T in table A.

As I said above this procedure is executed simultaneously by multiple users / connections.

How to use locking to make sure that each connection has a unique row and updates that row to set the status?

Will simply setting isolation level to repeatable read at the beginning of the procedure would do the magic?

Thanks

Saravanan K

If fetching & updating on single hit/scope then it is possible & acceptable with out any vulnerability. If these 2 operations are on separate hit/scope then it may cause deadlock/permanent lock on the SELECTED data.

You can get the flavor of sp_getapplock & sp_releaselock which is one of the good exercise for your requirement.

Sample,

Create proc LockedProcess(@.Data ..)

as

Begin

Exec sp_getapplock 'My Lock Name', 'Exclusive'

--Do Your Operation here

Exec sp_releaseapplock 'My Lock Name'

End

|||

Thanks a lot Manivannan.

The following is the outline of my proc.

CREATE PROC SVP_SELECTREPORTS

@.SVCTYPE VARCHAR(50)

AS

SET NOCOUNT ON

DECLARE @.ERRORCODE INT

DECLARE @.TEMPTABLE TABLE(ID INT,

FIELD1 VARCHAR(256),

FIELD2 CHAR(8),

FIELD3 VARCHAR(40),

FIELD4 VARCHAR(50),

FIELD5 CHAR(4),

FIELD6 TEXT,

FIELD7 TEXT,

FIELD8 DATETIME,

FIELD9 CHAR(1),

FIELD10 VARCHAR(40),

FIELD11 CHAR(1),

FIELD12 INT,

FIELD13 VARCHAR(40))

BEGIN

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

--90 DAYS

BEGIN TRAN

INSERT INTO @.TEMPTABLE

SELECT TOP 1 UR.* FROM

(

SELECT

ID,

FIELD1,

FIELD2,

FIELD3,

FIELD4' = CASE WHEN ISNULL(LTRIM(RTRIM(FIELD4)),'') = '' THEN '' ELSE '(' + FIELD4 + ')' END,

FIELD5,

FIELD6,

FIELD7,

FIELD8,

FIELD9,

'FIELD10' = 'MYTYPE',

FIELD11,

FIELD12,

FIELD13 = FIELD10

FROM MYORG

WHERE FIELD10 = '1'

AND FIELD9 = 'N' AND FIELD11 = 'N'

UNION ALL

SELECT

A.ID,

A.FIELD1,

A.FIELD2,

A.FIELD3,

A.FIELD4,

A.FIELD5,

A.FIELD6,

A.FIELD7,

A.FIELD8,

A.FIELD9,

'FIELD10' = R.NM,

A.FIELD11,

A.FIELD12,

'FIELD13' = A.TYPE_NM

FROM MYRPT R, MYORG A

WHERE A.FIELD10 NOT IN ('1','3') AND

R.ID = SUBSTRING(A.FIELD10,3,LEN(A.FIELD10) - 2)

AND A.FIELD9 = 'N' AND A.FIELD11 = 'N'

) UR ORDER BY UR.ID ASC

SELECT @.ERRORCODE = @.@.ERROR

IF (@.ERRORCODE <> 0)

GOTO ERROR

UPDATE MYORG SET FIELD9 = 'T' WHERE

ID = (SELECT ID FROM @.TEMPTABLE)

SELECT @.ERRORCODE = @.@.ERROR

IF (@.ERRORCODE <> 0)

GOTO ERROR

SELECT * FROM @.TEMPTABLE

END

IF THERE IS A PROBLEM ROLLBACK

ERROR:

IF (@.ERRORCODE <> 0)

BEGIN

ROLLBACK TRAN

PRINT 'UNEXPECTED ERROR OCCURED'

RETURN -100

END

--IF THERE ARE NO ERRORS AND THE EXECUTION REACHES THIS LINE COMMIT THE TRANSACTION

COMMIT TRAN

END

Do you think the locking is sufficient enough so that all users gets one unique row?

|||

Personally, I don't like it. I do recognize, however, that serialization is sometimes a necessary evil.

You are artificially creating a single point of 'blocking' behavior. This could cause major problems as the usage increases.

Normal 'best practice' is to remove and decrease points of blocking.

But if you must continue down the path of serialization, this thread, especially the code example, may help you.

You will want to minimize the code (including switching) that must execute while the AppLock is set.

In the second SELECT (of the UNION statement) there is what appears to be a cartesian JOIN -that may not be efficient.

It seems that the @.TempTable can be eliminated with a single UPDATE statetment with a WHERE clause wherein the [ID] is validated against the entire [SELECT TOP 1 UR.* FROM...] structure.

|||

Thanks Arnie .

If I eliminate the @.TempTable and update in a single statement, how do I return the

specific row to the calling application? [I am using SQL – 2000].

I am working on your other suggestions.

|||

If you were using SQL 2005, it would be easy.

Then you could just refer to Books Online about using the OUTPUT capability of an UPDATE statement.

Refer to Books Online, Topic: UPDATE

Scroll down to sub-topic: E. Using UPDATE with the OUTPUT clause.

However, since you are using SQL 2000, you will have to do it a bit differently.

The following code example will allow you to see a potential method that doesn't require a Temp table:

Code Snippet

USE Northwind
GO

BEGIN TRAN

DECLARE
@.LastName varchar(20),
@.FirstName varchar(20)

SELECT City
FROM Employees
WHERE EmployeeID = 5

UPDATE Employees
SET
City = 'MyTown',
@.LastName = LastName,
@.FirstName = FirstName
WHERE EmployeeID = 5

SELECT
@.LastName,
@.FirstName

SELECT City
FROM Employees
WHERE EmployeeID = 5

ROLLBACK TRAN

City
London

-- --
Buchanan Steven

City
MyTown

row level security without views

I want to implement row level security in SQL Server 2005 without
implementing views. The desired effect is firing a query on a table
like "select * from <tablename>" should return only rows which are
accessible to the logged in user. Which rows are accessible will be
decided using some data in another table. This table will have user
login name and one or more IDs associated with it. These IDs will be FK
in the table on which we fire select (or update/delete) query. I need
to device a solution such that the select query will return all rows
from the table which contain the ID that is assigned to the logged in
user. Is it possible in SQL Server 2005 without implmenting views on
each table?
Thanks in advance.
Nikhil.Hi
http://www.microsoft.com/technet/pr...5/multisec.mspx
"Nikhil" <nikhilukidwe@.gmail.com> wrote in message
news:1159253428.573628.105420@.d34g2000cwd.googlegroups.com...
>I want to implement row level security in SQL Server 2005 without
> implementing views. The desired effect is firing a query on a table
> like "select * from <tablename>" should return only rows which are
> accessible to the logged in user. Which rows are accessible will be
> decided using some data in another table. This table will have user
> login name and one or more IDs associated with it. These IDs will be FK
> in the table on which we fire select (or update/delete) query. I need
> to device a solution such that the select query will return all rows
> from the table which contain the ID that is assigned to the logged in
> user. Is it possible in SQL Server 2005 without implmenting views on
> each table?
> Thanks in advance.
> Nikhil.
>|||Thanks Uri,
I have already seen this article. This article explains the
"implementation" of row-level security using existing constructs like
tables,views and roles. I wanted to know if SQL Server 2005 has any
built-in support for the row level security and I think it is not
there.
Please correct me if I am wrong.
Nikhil.
Uri Dimant wrote:[vbcol=seagreen]
> Hi
> http://www.microsoft.com/technet/pr...5/multisec.mspx
>
> "Nikhil" <nikhilukidwe@.gmail.com> wrote in message
> news:1159253428.573628.105420@.d34g2000cwd.googlegroups.com...|||> tables,views and roles. I wanted to know if SQL Server 2005 has any
> built-in support for the row level security and I think it is not
> there.
> Please correct me if I am wrong.
Not , that I 'm aware
"Nikhil" <nikhilukidwe@.gmail.com> wrote in message
news:1159335152.141657.291800@.m7g2000cwm.googlegroups.com...
> Thanks Uri,
> I have already seen this article. This article explains the
> "implementation" of row-level security using existing constructs like
> tables,views and roles. I wanted to know if SQL Server 2005 has any
> built-in support for the row level security and I think it is not
> there.
> Please correct me if I am wrong.
> Nikhil.
> Uri Dimant wrote:
>

row level security in SQL

Hi Gurus, I am looking for row level security in sql
server. I was told that sql enterprise edtion is required
to implement this. can anyone give me some direction of
how this can be done? thanks.This is a multi-part message in MIME format.
--=_NextPart_000_00B3_01C37844.E0892760
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You don't need EE. Rather, you can create views and grant access to the =views. For example, if you wanted to restrict people's access to just =the rows they added - and you were tracking that information - then you =could have:
create view MyView
as
select
*
from
MyTable
where
CreateUser =3D CURRENT_USER
go
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"yl" <hubrian@.hotmail.com> wrote in message =news:16c601c37865$143b3380$a601280a@.phx.gbl...
Hi Gurus, I am looking for row level security in sql server. I was told that sql enterprise edtion is required to implement this. can anyone give me some direction of how this can be done? thanks.
--=_NextPart_000_00B3_01C37844.E0892760
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You don't need EE. Rather, you =can create views and grant access to the views. For example, if you wanted to =restrict people's access to just the rows they added - and you were =tracking that information - then you could have:
create view MyView
as
select
=*
from
=MyTable
where
CreateUser ==3D CURRENT_USER
go
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"yl" wrote in message news:16c601c37865$14=3b3380$a601280a@.phx.gbl...Hi Gurus, I am looking for row level security in sql server. I was told =that sql enterprise edtion is required to implement this. can anyone give =me some direction of how this can be done? thanks.

---

--=_NextPart_000_00B3_01C37844.E0892760--|||thank you all very much.
>--Original Message--
>Hi Gurus, I am looking for row level security in sql
>server. I was told that sql enterprise edtion is
required
>to implement this. can anyone give me some direction of
>how this can be done? thanks.
>.
>

row level security

I am looking for a way to implement row level security on my SQL Server 2005 Express database. Thanks in advance for any input.

Row Level Security Defined

Why do I need row level security?

As an architect, developer, or operator of a computer based information system, you mustprotect the sensitive data that you manage. To protect the data, you must ensure that the users of your system have the authority toperform thefunctions they attempt in your system to view, update, delete, insert, and grant privileges onyour system's data.

Depending on the approach you take, this requirementfor data access securitycan dramatically increase the time and expense associated with building and operating your information system.

Row level security (for purposes of this discussion) is a scheme that enforcesdata access security for the rows in a relational database table via a set ofpermission grants(both read and write)assigned to auser or to agroup of users.

The identity ofa user is established(user authentication)when the user makes a connection to the database. The databasealreadynatively supports access control at the object level (table, view, etc.) and even the table column level, but not at the table row level, making row level security an additional scheme implemented on top of the base database product.

When the row level security scheme is in place and functioning, the userwillonly see the rows to which they have at least read level access. They are onlyable to updateand deletedata ina row if they have write level access to the row. They are only able to insert data in a table if they have been granted insert privileges on the table.

From a development cost perspective, selection of atransparent row level security schemeis extremely important – i.e. theSQLSELECT, INSERT, UPDATE and DELETE statements that run against the tables prior to implementing the row level security scheme continue towork without being modified when the row level security scheme is in place and functioning.

A significant reduction in administrative overhead can be achieved if the row level security scheme supports apermission inheritance model where the rows in a child table automatically inherit the permission grants to the parent row in the parent table. This pattern can be repeated for grand children, great grandchildren, etc. This feature makes it possible to create a grant to a single row in a parent table and automatically grant access to all of the appropriate rows in child tables, grandchild tables, etc.

This type of permission control is referred to asjoin-based security because the permissions for a particular row in a table are determined using relational join criteria to locate and inherit permissions from related rows in another table.

Maintainingand operating the information system is greatly simplified ifdata access securityis enforced by definingpermissiongrants to agroups of users (instead of directly assigning the grants to individual users). This separatespermissions configuration andmembership configuration into two distinct tasks.

You can look athttp://www.datanomad.com orhttp://www.technicalmedia.com for more information on row level security in SQL Server 2005.

ROW LEVEL SECURITY

How can I implement "Row Level Security" in SQL Server 2000?

Thanks alot.Generally speaking, you can create views to horizontally partition data
based on your security requirements. The views filter data so that only
authorized users can access data. Do not allow direct access to the
underlying tables and grant user permissions only on views.

The script below illustrates this technique to implement security so that
users can only access employee data for those departments they are allowed
to see, based on their login and entries in the SecurityByDepartment table.
This approach can be extended to include application-defined roles in order
to reduce security administration.

SET NOCOUNT ON
GO

CREATE TABLE Employees
(
EmployeeId int NOT NULL
CONSTRAINT PK_Employee
PRIMARY KEY NONCLUSTERED,
DepartmentId int NOT NULL,
SomeData varchar(30)
)
CREATE CLUSTERED INDEX Employees_cdx ON Employees(DepartmentId)
GO

CREATE TABLE SecurityByDepartment
(
UserName sysname NOT NULL,
DepartmentId int NOT NULL,
CONSTRAINT PK_SecurityByDepartment
PRIMARY KEY (UserName, DepartmentId)
)
GO

CREATE VIEW MyEmployees
AS
SELECT
e.EmployeeId,
e.DepartmentId,
e.SomeData
FROM Employees e
JOIN SecurityByDepartment sbd ON
sbd.UserName = SUSER_SNAME() AND
sbd.DepartmentId = e.DepartmentId
GO

EXEC sp_addrole 'MyRole'
GRANT SELECT ON MyEmployees TO MyRole
GO

INSERT INTO Employees VALUES(1, 1, 'some data 1')
INSERT INTO Employees VALUES(2, 1, 'some data 2')
INSERT INTO Employees VALUES(3, 1, 'some data 3')
INSERT INTO Employees VALUES(4, 2, 'some data 4')
INSERT INTO Employees VALUES(5, 2, 'some data 5')
GO

INSERT INTO SecurityByDepartment VALUES('Login1' ,1)
INSERT INTO SecurityByDepartment VALUES('Login1' ,2)
INSERT INTO SecurityByDepartment VALUES('Login2' ,1)
INSERT INTO SecurityByDepartment VALUES('Login3' ,2)
GO

EXEC sp_addlogin 'Login1'
EXEC sp_grantdbaccess 'Login1'
EXEC sp_addrolemember 'MyRole', 'Login1'
EXEC sp_addlogin 'Login2'
EXEC sp_grantdbaccess 'Login2'
EXEC sp_addrolemember 'MyRole', 'Login2'
EXEC sp_addlogin 'Login3'
EXEC sp_grantdbaccess 'Login3'
EXEC sp_addrolemember 'MyRole', 'Login3'
GO

PRINT SUSER_SNAME()
SELECT * FROM MyEmployees
GO

SETUSER 'Login1'
PRINT SUSER_SNAME()
SELECT * FROM MyEmployees
GO

SETUSER
SETUSER 'Login2'
PRINT SUSER_SNAME()
SELECT * FROM MyEmployees
GO

SETUSER
SETUSER 'Login3'
PRINT SUSER_SNAME()
SELECT * FROM MyEmployees
GO

SETUSER
GO

DROP VIEW MyEmployees
DROP TABLE Employees
DROP TABLE SecurityByDepartment
EXEC sp_revokedbaccess 'Login1'
EXEC sp_droplogin 'Login1'
EXEC sp_revokedbaccess 'Login2'
EXEC sp_droplogin 'Login2'
EXEC sp_revokedbaccess 'Login3'
EXEC sp_droplogin 'Login3'
EXEC sp_droprole 'MyRole'
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Elham.Ghoddousi" <elham_gh@.yahoo.com> wrote in message
news:4c3908a6.0409040503.43a24c3d@.posting.google.c om...
> How can I implement "Row Level Security" in SQL Server 2000?
> Thanks alot.

Row Level Security

Hello,
I want to implement row level security using sql server reporting
services.
I noticed another post asking the same question and the same subject
line, but it recieved no proper answer.
My situation is the same as his - I need to seperate data according to
the client user name, from the same table.
The internet system i am building, will need to handle many users,
which can't see each other's records, but their data is stored at the
same table.
Here is a link to the other post on the same subject, i find it not
relevant to post the same thing twice.
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/messages/110300aa490910ed,de31f976623b6c89,3c78bd588c6aba03,f67dcd82d3646a3a,4761bb89bab82df9,448882c85246ac99?thread_id=f623781fb429616f&mode=thread&noheader=1&q=row+security#doc_110300aa490910ed
Thanks in advance,
ShlomoCan you use User!UserID.Value as a join key instead of Suser_Sname() as you
might do in T-SQL
I haven't test User! so I do not know whose id it is but I beleive it is
intended to be the report requestor (when using trusted security)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<shlomoid@.gmail.com> wrote in message
news:1108300806.467922.236190@.o13g2000cwo.googlegroups.com...
> Hello,
> I want to implement row level security using sql server reporting
> services.
> I noticed another post asking the same question and the same subject
> line, but it recieved no proper answer.
> My situation is the same as his - I need to seperate data according to
> the client user name, from the same table.
> The internet system i am building, will need to handle many users,
> which can't see each other's records, but their data is stored at the
> same table.
> Here is a link to the other post on the same subject, i find it not
> relevant to post the same thing twice.
> http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/messages/110300aa490910ed,de31f976623b6c89,3c78bd588c6aba03,f67dcd82d3646a3a,4761bb89bab82df9,448882c85246ac99?thread_id=f623781fb429616f&mode=thread&noheader=1&q=row+security#doc_110300aa490910ed
> Thanks in advance,
> Shlomo
>|||Thanks alot for the answer, this really does seem like something that
will prove to be usefull.
I have but another extension to this question:
The reporting services server does not provide authentication, only
authorization (as the rs books online state).
That means, that i need to create a user on the machine, a real windows
user, for each internet user i want to recieve some sort of
authorization.
This is of course a source of many problems, such as kinds of security
issues.
What is the common practice in this scenario?
How can i implement my own authentication mechanizm? And should I?
Thanks again,
Shlomo

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.

Row and Cell Security

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.

You may need to prefix the table name by its schema name.

Thanks
Laurentiu|||I think is the ToSrting that is giving the error do you know what this meens?

Thanks|||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?|||Have you looked into prefixing the table name with the schema name? The message that you obtained indicates that the table name lookup failed. The SELECT didn't even get to process the "ID, Label.ToString()" part, so even if there would be errors in it, they're not the ones generating the message that you received.

Thanks
Laurentiu|||

But what do you mean by scheam? Is it the code that generates the database? were 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.

|||

A schema is a new concept that helps managing the contents of a database. See the "User-Schema Separation" topic in Books Online.

To find the schema name for this table, you can try the following query:

select schema_name(schema_id) from sys.objects where name = 'tblUniqueLabel'

This will list all schemas in which you can find objects named tblUniqueLabel.

But now that you mentioned that you could create another view on the table, I took a closer look at the create statement and it may be incorrect. Try executing the following:

SELECT ID, Label.ToString()
FROM tblUniqueLabel tUL 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 = tUL.ID)
GO

I didn't notice that you had a second reference to tblUniqueLabel in the inner query. That may be the one generating the error. Let us know if this works. If this doesn't work, please also post the create table statements that you used to create the tblUniqueLabel and tblUniqueLabelMarking tables.

Thanks

Laurentiu

|||

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

|||

You've changed the query, so you're getting different errors now. Note that the original error, as I have mentioned in my previous message, was not related to the schema, but to the way the inner clause of the query was written. You don't need to be explicit about the dbo schema, this is one of the schemas searched by default.

What is the type of the Label column? I've looked over the article but it doesn't specify this. In T-SQL, to convert from a type to another, you would need to use the CONVERT function. The ToString method indicates Label is a CLR user defined type. If you've just defined it as a SQL builtin type, then this won't work.

Thanks
Laurentiu

|||

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.

|||

I don't know how the CLR type is defined and the paper does not seem to describe it. I spoke with one of the authors and they are working to release some additional material that will allow implementing the solution described in the white paper. I don't yet have a date for when this will happen, but I am trying to find one. It will most likely happen next year though. I'll post to this thread when I will have more information.

Thanks
Laurentiu

|||

Thank you anyway.

But i do have another problem with the database menber describe in the white paper, it says in the paper that we have to add as menber of the role the child role. But when you go to add a menber a the role you can not add a role. How is it possible to do so?

|||

Please open a new thread on this issue and provide more details on what commands you are trying and what error messages you receive.

Thanks
Laurentiu

|||

Please feel free to explore the free eval of Data Nomad ( http://www.technicalmedia.com ). This product will automatically generate views for row level security in SQL Server. It works with any version including SQL Server 2005 Express.

You can look at the schemas, views synonyms and stored procedures that are created to learn more about how to build your own (or of course you can use the product too $100 developer - no runtime costs :))

Row-Level Security for Microsoft SQL Server 2005

Data Nomad? is an affordable set of developer tools that extend the Microsoft SQL Server 2005 platform to provide row-level security and remote access features allowing developers to accurately and efficiently create and manage powerful distributed applications that insure access to information is protected.

Developers of .NET 1.1 and .NET 2.0 smart client and web applications can now easily add row-level security to database applications through the Data Nomad? developer tools. Existing databases are easily configured by identifying the tables to be protected and by creating row-level permission grants.

The same (unmodified) SQL statements work against the Nomad database extensions. The extended database appears to only contain the rows to which the user has at least read permissions. Database updates and deletes only succeed against rows to which the user has owner permissions.

This type of seamless integration is achieved by leveraging two powerful new features of Microsoft SQL Server 2005: the schema (a collection of database objects that form a single namespace) and the synonym (an alternative name for another database object providing a layer of abstraction over the original object).

The Nomad extensions support both SQL Server authentication and Integrated NT authentication for database connections, and support local, LAN-connected, and Web-connected backend databases.

“Using Technical Media’s Nomad product has saved us months of development” said Darcy Vaughan, a founder and Director of PetroWEB, Inc.

PetroWEB, Inc has obtained an exclusive Data Nomad? license for the upstream oil and gas industry. For information on utilizing Data Nomad? technology in this industry, please contact Darcy Vaughan at 303.308.9100.

Row and Cell Security

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.

You may need to prefix the table name by its schema name.

Thanks
Laurentiu|||I think is the ToSrting that is giving the error do you know what this meens?

Thanks|||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?|||Have you looked into prefixing the table name with the schema name? The message that you obtained indicates that the table name lookup failed. The SELECT didn't even get to process the "ID, Label.ToString()" part, so even if there would be errors in it, they're not the ones generating the message that you received.

Thanks
Laurentiu|||

But what do you mean by scheam? Is it the code that generates the database? were 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.

|||

A schema is a new concept that helps managing the contents of a database. See the "User-Schema Separation" topic in Books Online.

To find the schema name for this table, you can try the following query:

select schema_name(schema_id) from sys.objects where name = 'tblUniqueLabel'

This will list all schemas in which you can find objects named tblUniqueLabel.

But now that you mentioned that you could create another view on the table, I took a closer look at the create statement and it may be incorrect. Try executing the following:

SELECT ID, Label.ToString()
FROM tblUniqueLabel tUL 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 = tUL.ID)
GO

I didn't notice that you had a second reference to tblUniqueLabel in the inner query. That may be the one generating the error. Let us know if this works. If this doesn't work, please also post the create table statements that you used to create the tblUniqueLabel and tblUniqueLabelMarking tables.

Thanks

Laurentiu

|||

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

|||

You've changed the query, so you're getting different errors now. Note that the original error, as I have mentioned in my previous message, was not related to the schema, but to the way the inner clause of the query was written. You don't need to be explicit about the dbo schema, this is one of the schemas searched by default.

What is the type of the Label column? I've looked over the article but it doesn't specify this. In T-SQL, to convert from a type to another, you would need to use the CONVERT function. The ToString method indicates Label is a CLR user defined type. If you've just defined it as a SQL builtin type, then this won't work.

Thanks
Laurentiu

|||

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.

|||

I don't know how the CLR type is defined and the paper does not seem to describe it. I spoke with one of the authors and they are working to release some additional material that will allow implementing the solution described in the white paper. I don't yet have a date for when this will happen, but I am trying to find one. It will most likely happen next year though. I'll post to this thread when I will have more information.

Thanks
Laurentiu

|||

Thank you anyway.

But i do have another problem with the database menber describe in the white paper, it says in the paper that we have to add as menber of the role the child role. But when you go to add a menber a the role you can not add a role. How is it possible to do so?

|||

Please open a new thread on this issue and provide more details on what commands you are trying and what error messages you receive.

Thanks
Laurentiu

|||

Please feel free to explore the free eval of Data Nomad ( http://www.technicalmedia.com ). This product will automatically generate views for row level security in SQL Server. It works with any version including SQL Server 2005 Express.

You can look at the schemas, views synonyms and stored procedures that are created to learn more about how to build your own (or of course you can use the product too $100 developer - no runtime costs :))

Row-Level Security for Microsoft SQL Server 2005

Data Nomad? is an affordable set of developer tools that extend the Microsoft SQL Server 2005 platform to provide row-level security and remote access features allowing developers to accurately and efficiently create and manage powerful distributed applications that insure access to information is protected.

Developers of .NET 1.1 and .NET 2.0 smart client and web applications can now easily add row-level security to database applications through the Data Nomad? developer tools. Existing databases are easily configured by identifying the tables to be protected and by creating row-level permission grants.

The same (unmodified) SQL statements work against the Nomad database extensions. The extended database appears to only contain the rows to which the user has at least read permissions. Database updates and deletes only succeed against rows to which the user has owner permissions.

This type of seamless integration is achieved by leveraging two powerful new features of Microsoft SQL Server 2005: the schema (a collection of database objects that form a single namespace) and the synonym (an alternative name for another database object providing a layer of abstraction over the original object).

The Nomad extensions support both SQL Server authentication and Integrated NT authentication for database connections, and support local, LAN-connected, and Web-connected backend databases.

“Using Technical Media’s Nomad product has saved us months of development” said Darcy Vaughan, a founder and Director of PetroWEB, Inc.

PetroWEB, Inc has obtained an exclusive Data Nomad? license for the upstream oil and gas industry. For information on utilizing Data Nomad? technology in this industry, please contact Darcy Vaughan at 303.308.9100.

ROUND function

ROUND(12.5, 0) return 12
How implement in T-SQL (SQL Server 2000) to return 13?SELECT ROUND(12.5, 0)
Returns 13 !
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Microsoft" <vali_albastroiu@.hotmail.com> wrote in message
news:OhLQppmmFHA.1416@.TK2MSFTNGP09.phx.gbl...
> ROUND(12.5, 0) return 12
> How implement in T-SQL (SQL Server 2000) to return 13?
>|||ROUND(12.5, 0, 1) returns 12
ROUND(12.5, 0, 0) returns 13
"Microsoft" <vali_albastroiu@.hotmail.com> wrote in message
news:OhLQppmmFHA.1416@.TK2MSFTNGP09.phx.gbl...
> ROUND(12.5, 0) return 12
> How implement in T-SQL (SQL Server 2000) to return 13?
>