Imitating ownership possible?

In SQL Server 2000 you could use the SETUSER command
(http://msdn2.microsoft.com/en-us/li...40(SQL.80).aspx) to
impersonate the user context for a SQL user account. However, this can only
be used by a sysadmin or db_owner and could not be used for Windows
accounts. In SQL Server 2005 you can use the EXECUTE AS clause to change the
execution context.

Here is a good article about EXECUTE AS:
http://blogs.msdn.com/sqlserverstor.../21/642061.aspx
HTH,

Plamen Ratchev
http://www.SQLStudio.comAwesome. Thanks for the quick response. I've got a lot of experience
in SQL in other languages, and some in SQL Server, but all the little
tricks of the trade I'm still short on.

Thanks again.

J

imitating nested "FOREACH" loop in SQL Query

Dear All,

I need to create a query to list all the subfolders within a folder.

I have a database table that lists the usual properties of each of the folder.

I have another database table that has two columns

1. Parent folder
2. Child folder

But this table maintains the parent child relationship only to one level.

For example if i have a folder X that has a subfolder Y and Z.
And Y has subfolders A and B.
and B has subfolder C and D
and C has subfolder E and F

The database table will look like

parentfolder child folder
X Y
X Z
Y A
Y B
B C
B D
C E
C F

I want to write a query which will take a folder name as the input and will provide me a list of all the folders and subfolders under it. The query should be based on the table (parent - child) and there should not be any restriction on the subfolder levels to search and report for.

I have been banging my head to do this but i have failed so far. Any help on this will be highly appreciated.

The APPLY operator will do what you need.

Check out:

http://msdn2.microsoft.com/en-us/library/ms175156.aspx

For a description and an example that pretty much is like your needs.

|||

In sql server 2005 you can use CTE..

Code Snippet

Create Table #folder (

[parentfolder] Varchar(100) ,

[childfolder] Varchar(100)

);

Insert Into #folder Values('X','Y');

Insert Into #folder Values('X','Z');

Insert Into #folder Values('Y','A');

Insert Into #folder Values('Y','B');

Insert Into #folder Values('B','C');

Insert Into #folder Values('B','D');

Insert Into #folder Values('C','E');

Insert Into #folder Values('C','F');

;With CTE([parentfolder],[childfolder],[Level],[Paths]) as

(

Select [parentfolder],[childfolder], 1 Level, Cast(Parentfolder + '\' + childfolder as varchar) Paths From #folder Where parentfolder = 'X'

UNION ALL

Select data.[parentfolder],data.[childfolder], Level + 1,Cast(Paths + '\' + data.[childfolder] as varchar)From #folder Data Join CTE On Data.ParentFolder = CTE.childfolder

)

Select * from CTE Order By Paths

|||Nicely done Mani!