User Permissions LiveReport in MSSql

Has anyone created a SQL query for filtering DTree data on permissions in MSSQL ? Looking for something equivalent to "Connect to " in Oracle.

Find more posts tagged with

Comments

eLink User

Message from Marie Lindsay <<A HREF="mailto:marie@opentext.com">marie@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

In MSSQL, you need to create a stored procedure to achieve a CONNECT BY kind of result. I read an article at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_5yk3.asp that was useful. We discuss this in a little more detail in the Advanced Schema and LiveReports course, but not much -- it's mostly a matter of knowing how to create stored procedures in MSSQL.

Here's an example -- it won't work in a LiveReport, but it'll give you a general idea. It doesn't address permissions; it just lists the items under a folder (using a passed in folderid). To execute the procedure, you could enter "exec itemsunderfolder2 xxxx" from Query Analyzer, where xxxx is a folder's dataid.

create proc itemsunderfolder2 @currentid int as set nocount on declare @lvl int, @currentname char(40), @line char(100) select @currentname = name from dtree where dataid = @currentid create table #stack (itemid int, item char(40), lvl int) insert into #stack values (@currentid, @currentname, 1) select @lvl = 1 while @lvl > 0 begin if exists( select * from #stack where lvl = @lvl ) begin select @currentid = itemid, @currentname = item from #stack where lvl = @lvl select @line = space(@lvl-1) + @currentname print @line delete from #stack where lvl = @lvl and itemid = @currentid insert #stack select c.dataid, substring(c.name, 1, 30), @lvl + 1 from dtree p, dtree c where p.dataid = c.parentid and c.parentid = @currentid @rowcount > 0 select @lvl = @lvl + 1 end else select @lvl = @lvl - 1 end ? while

Marie Lindsay

-----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, June 28, 2002 9:21 AM
To: eLink Recipient
Subject: User Permissions LiveReport in MSSql

User Permissions LiveReport in MSSql Posted by elpasoITuser on 06/28/2002 10:15 AM

Has anyone created a SQL query for filtering DTree data on permissions in MSSQL ? Looking for something equivalent to "Connect to " in Oracle.

ELPASOUser1_(Delete)_2549570

Thanks Marie. I was hoping if I could avoid creating procedures. Looks like I have no choice.Thanks..