Read Only User - One Database

How do I create a user, that has rights to only one database, and only
select rights to some views?
I created a login in Enterprise Manager; checked the database access only on
the database the user needs to see; and checked the db_datareader role.
However, logging in as this user I can see and select from other databases.
Under the database I gave the user rights to, I checked only select rights
on the views I want the user to select from. I can still select from
tables, etc.
Obviously, I'm missing something here...Which other databases? Most likely those are databases with
the guest account enabled. If a user doesn't have an account
to log into the database and the guest account is enabled,
the user has access through the guest account and whatever
rights are granted to public and this account. The guest
account cannot be deleted from master or tempdb. It can be
added, delete from other databases.
When you added the user to the db_datareader role, you gave
that user permissions to select from all user tables.
Permissions are cumulative so the user obtains all
permissions through the combination of their individual
account and any groups, roles that they are members of. When
you also gave the individual account select permission on
views, the user ended up with those permissions as well as
select on all user tables.
-Sue
On Thu, 28 Oct 2004 17:39:38 -0400, "Adrian Maull \(MCP\)"
<no_spam@.no_email.org> wrote:

>How do I create a user, that has rights to only one database, and only
>select rights to some views?
>I created a login in Enterprise Manager; checked the database access only o
n
>the database the user needs to see; and checked the db_datareader role.
>However, logging in as this user I can see and select from other databases.
>Under the database I gave the user rights to, I checked only select rights
>on the views I want the user to select from. I can still select from
>tables, etc.
>Obviously, I'm missing something here...
>|||sue i can't see guest account except master database.. you meant somebody
created it ?
"Sue Hoegemeier" wrote:

> Which other databases? Most likely those are databases with
> the guest account enabled. If a user doesn't have an account
> to log into the database and the guest account is enabled,
> the user has access through the guest account and whatever
> rights are granted to public and this account. The guest
> account cannot be deleted from master or tempdb. It can be
> added, delete from other databases.
> When you added the user to the db_datareader role, you gave
> that user permissions to select from all user tables.
> Permissions are cumulative so the user obtains all
> permissions through the combination of their individual
> account and any groups, roles that they are members of. When
> you also gave the individual account select permission on
> views, the user ended up with those permissions as well as
> select on all user tables.
> -Sue
> On Thu, 28 Oct 2004 17:39:38 -0400, "Adrian Maull \(MCP\)"
> <no_spam@.no_email.org> wrote:
>
>|||No...nothing about someone creating the guest account but I
don't know what databases the user is accessing. Which other
databases? System databases or user databases? Is the user a
member of other roles or groups that would give them access
to the other databases?
-Sue
On Thu, 28 Oct 2004 19:53:01 -0700, "Dave"
<Dave@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>sue i can't see guest account except master database.. you meant somebody
>created it ?
>"Sue Hoegemeier" wrote:
>|||I've deleted the guest account from other user DBs and the user I can not
see/connect to those DBs - good.
I've unchecked the db_datareader role and only gave select permissions to 2
views in the DB - that seems to work OK as well.
However, the user can still select from system tables in the database they
are assigned to. Any way to prevent that?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:og93o0loelonsc0tkhbhau449s6defp78l@.
4ax.com...
> Which other databases? Most likely those are databases with
> the guest account enabled. If a user doesn't have an account
> to log into the database and the guest account is enabled,
> the user has access through the guest account and whatever
> rights are granted to public and this account. The guest
> account cannot be deleted from master or tempdb. It can be
> added, delete from other databases.
> When you added the user to the db_datareader role, you gave
> that user permissions to select from all user tables.
> Permissions are cumulative so the user obtains all
> permissions through the combination of their individual
> account and any groups, roles that they are members of. When
> you also gave the individual account select permission on
> views, the user ended up with those permissions as well as
> select on all user tables.
> -Sue|||The public role is able to select from some of the system tables. All
database user are members of public. That's why the user can select
from some of the system tables.
-Sue
On Fri, 29 Oct 2004 08:55:09 -0400, "Adrian Maull \(MCP\)"
<no_spam@.no_email.org> wrote:

>I've deleted the guest account from other user DBs and the user I can not
>see/connect to those DBs - good.
>I've unchecked the db_datareader role and only gave select permissions to 2
>views in the DB - that seems to work OK as well.
>However, the user can still select from system tables in the database they
>are assigned to. Any way to prevent that?
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:og93o0loelonsc0tkhbhau449s6defp78l@.
4ax.com...
>|||Adrian
In additon to Sue's advice ,if you don't want the users be able to select
from system tables , you can hide them by editing SQL Server registration
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:m745o01h1ck4l9e3262931g9nktach4ru3@.
4ax.com...
> The public role is able to select from some of the system tables. All
> database user are members of public. That's why the user can select
> from some of the system tables.
> -Sue
> On Fri, 29 Oct 2004 08:55:09 -0400, "Adrian Maull \(MCP\)"
> <no_spam@.no_email.org> wrote:
>
2[vbcol=seagreen]
they[vbcol=seagreen]
>|||That doesn't prevent users from being able to select from
system tables though. All that will do is prevent displaying
them in Enterprise Manager.
-Sue
On Sun, 31 Oct 2004 11:20:42 +0200, "Uri Dimant"
<urid@.iscar.co.il> wrote:

>Adrian
>In additon to Sue's advice ,if you don't want the users be able to select
>from system tables , you can hide them by editing SQL Server registration
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:m745o01h1ck4l9e3262931g9nktach4ru3@.
4ax.com...
>2
>they
>|||Yep, I assumed that all manipulations are done by users in EM.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:qi5bo0lo2223mjjdqcfkdlhvafhlg596i0@.
4ax.com...
> That doesn't prevent users from being able to select from
> system tables though. All that will do is prevent displaying
> them in Enterprise Manager.
> -Sue
> On Sun, 31 Oct 2004 11:20:42 +0200, "Uri Dimant"
> <urid@.iscar.co.il> wrote:
>
not[vbcol=seagreen]
to[vbcol=seagreen]
>|||I am trying to do the same thing. I have a user that needs access to one of
the databases on the server. I can prevent him from modifying or even seeing
the tables on the other databases with permissions, but I want to be able to
hide the other databases from Enterprise Manager. I am using SQL logins. Can
I do this? It sounds like you were able to do this. I have deleted the guest
account from a number of databases and added his login to only two databases
but the login displays all of the databases, including those with only sa as
the login.
"Adrian Maull (MCP)" wrote:

> I've deleted the guest account from other user DBs and the user I can not
> see/connect to those DBs - good.
> I've unchecked the db_datareader role and only gave select permissions to
2
> views in the DB - that seems to work OK as well.
> However, the user can still select from system tables in the database they
> are assigned to. Any way to prevent that?
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:og93o0loelonsc0tkhbhau449s6defp78l@.
4ax.com...
>
>

RE: Creating A Duplicate Database Without Enterprise Manager

Hi There,

I was wondering if there was a simple way of create a duplicate of an entire database.

I often need to create a second copy of a database for development purposes. I have lost the use of Enterprise Manager which I can no longer simply script the database and then use dts to move the data across.

Is there a way of doing this in T-SQL?

Thanks

SteveTake a full backup and restore as a different name ... that should do it .. i think ... rather than going about it the other way round.|||You lost the use of Enterprise Manager?

HUH?|||I think they're an amputee|||Perhaps it was destroyed by a Klingon Manager?|||Or a Romulan cloaking device has been attached, so it's really there, he just can't see it ;-)|||Could have been an Organian illusion all along...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55463

Re: Any one help me out!

Hi

I have a requirement in SSRS where in i need to generate a report in which initially there will be one record (say, a manager of a department and some values corresponding to him ) now i need to provide a drill-down on this employee(mamager) such that the next level of employees reporting to him should be displayed with all the parameters and this has to continue till the leaf level employee.

Thanks in advance.

will the number of levels be known in advance or is it parent-child?

RS is not very good at dealing with an arbitary number of grouping levels.

One way to do it would be define a report with a manager parameter that renders a table of subordiantes. Then in a master report, nest the subreport in a table for all expected levels passing the employee id to the subreport. Problem with that is that it wouldn't export very well.

The other ooption would be to PIVOT the parent-child table i.e. flatten it out into columns per each level, then build your table based on that.

Either way you'll need to know the number of levels in advance.

|||

Yes the number of levels will be known in advance.

i'm a novice in SSRS and does not much about it so can you throw some light on what you have just said.

|||

Please tell me the structure of the data and we'll take it from there.

Is it SQL or OLAP? A table or a dimension? what are the columns or levels?

|||

Hi adam,

I'll get an employee id as a parameter to the stored procedure and then i'll get a dataset which will contain all the data for me for all the employees.

Then from this data set i need to generate the drill down report as

(the root level employee)

(then his subordinates)

(then for each employee in the above level we need to get the lower levels here.)

............................................

And this hierarchy goes on till the last employee.

Thanks in advance.|||

ok, I'll ask the question again.

In format does the data come back? What are the columns?

is it employee_id, manager_id (where manager _id is the employee_id of the manager)

OR

is it employee_level_1_id, employee_level_2_id, employee_level_3_id,......

The latter is what you want to end up with to structure your report.

|||

Hi adam,

The data is in the following format

EmployeeID ManagerID Level(the level of the employee)

Thanks in advance.

|||

OK what about the top level manager, is his manager_id = employee_id or is it NULL?

Does the Level column representative of the number of levels in the hierarchy? In it numeric or a textual descriptor?

I assume you also have employee_name an well as some other employee information.

Can you post a little bit of dummy data?

|||

hi

top level managertop level manager is NULL and you will have columns like

EMP_NO , EMP_NAME , MGR_NO , LEVEL(int) , and some other fields............................

12345 xyz 32141 1

level ranges from 1-some known value

|||

The following is a code example of my implementation. Copy the xml into a file and save as .RDL.

You may need to modify the connection as locally I have SQL 2005 installed as a named instance.

The code flattens a table by performing a join onto itself for every level in the hierarchy. I'll try to come up with a neater solution because it feels like I should be able to use the new PIVOT functionality of SQL.

[code]

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="ds_master">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Data Source=.\sql2005;Initial Catalog=master</ConnectString>
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>008a7b74-651a-4e19-ad0d-a1af34c884ef</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>2.5cm</BottomMargin>
<RightMargin>2.5cm</RightMargin>
<PageWidth>21cm</PageWidth>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ColumnSpacing>1cm</ColumnSpacing>
<ReportItems>
<Table Name="table1">
<DataSetName>dst_employee</DataSetName>
<Top>0.91429cm</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="l1_employee_level">
<rd:DefaultName>l1_employee_level</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>#6e9eca</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l1_employee_level.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l1_employee_id">
<rd:DefaultName>l1_employee_id</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l1_employee_id.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l1_employee_name">
<rd:DefaultName>l1_employee_name</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l1_employee_name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.53333cm</Height>
</TableRow>
</TableRows>
</Header>
<Sorting>
<SortBy>
<SortExpression>=Fields!l1_employee_level.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="table1_l1_employee_level">
<GroupExpressions>
<GroupExpression>=Fields!l1_employee_id.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
<TableGroup>
<Visibility>
<ToggleItem>l1_employee_level</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="l2_employee_level">
<rd:DefaultName>l2_employee_level</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SlateGray</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l2_employee_level.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l2_employee_id">
<rd:DefaultName>l2_employee_id</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l2_employee_id.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l2_employee_name">
<rd:DefaultName>l2_employee_name</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l2_employee_name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.53333cm</Height>
</TableRow>
</TableRows>
</Header>
<Sorting>
<SortBy>
<SortExpression>=Fields!l2_employee_level.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="table1_l2_employee_level">
<Filters>
<Filter>
<Operator>NotEqual</Operator>
<FilterValues>
<FilterValue>0</FilterValue>
</FilterValues>
<FilterExpression>=Iif(Fields!l2_employee_id.Value, CStr(Fields!l2_employee_id.Value), "0")</FilterExpression>
</Filter>
</Filters>
<GroupExpressions>
<GroupExpression>=Fields!l2_employee_id.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
<TableGroup>
<Visibility>
<ToggleItem>l2_employee_level</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="l3_employee_level">
<rd:DefaultName>l3_employee_level</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>#8fa0b0</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l3_employee_level.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l3_employee_id">
<rd:DefaultName>l3_employee_id</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l3_employee_id.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l3_employee_name">
<rd:DefaultName>l3_employee_name</rd:DefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l3_employee_name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.53333cm</Height>
</TableRow>
</TableRows>
</Header>
<Sorting>
<SortBy>
<SortExpression>=Fields!l3_employee_level.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="table1_l3_employee_level">
<Filters>
<Filter>
<Operator>NotEqual</Operator>
<FilterValues>
<FilterValue>0</FilterValue>
</FilterValues>
<FilterExpression>=Iif(Fields!l3_employee_id.Value, CStr(Fields!l3_employee_id.Value), "0")</FilterExpression>
</Filter>
</Filters>
<GroupExpressions>
<GroupExpression>=Fields!l3_employee_id.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<ZIndex>1</ZIndex>
<Width>7.61904cm</Width>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>level</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>id</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>name</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.55873cm</Height>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<TableColumns>
<TableColumn>
<Width>2.53968cm</Width>
</TableColumn>
<TableColumn>
<Width>2.53968cm</Width>
</TableColumn>
<TableColumn>
<Width>2.53968cm</Width>
</TableColumn>
</TableColumns>
<Height>2.15872cm</Height>
</Table>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>20pt</FontSize>
<Color>SteelBlue</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.91429cm</Height>
<Value>Report3</Value>
</Textbox>
</ReportItems>
<Height>3.60635cm</Height>
</Body>
<rd:ReportID>0b7fa61b-6ddf-4332-bf17-5b42a10a7437</rd:ReportID>
<LeftMargin>2.5cm</LeftMargin>
<DataSets>
<DataSet Name="dst_employee">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>WITH employee AS
(
SELECT employee_id = 1, employee_name = 'Manager 1', manager_id = NULL, level_no = 1
UNION ALL
SELECT employee_id = 2, employee_name = 'Sub Manager 1', manager_id = 1, level_no = 2
UNION ALL
SELECT employee_id = 3, employee_name = 'Leaf Employee 1', manager_id = 2, level_no = 3
UNION ALL
SELECT employee_id = 4, employee_name = 'Leaf Employee 2', manager_id = 1, level_no = 2
)
SELECT l1_employee_id = l1.employee_id
, l1_employee_name = l1.employee_name
, l1_employee_level = l1.level_no

, l2_employee_id = l2.employee_id
, l2_employee_name = l2.employee_name
, l2_employee_level = l2.level_no

, l3_employee_id = l3.employee_id
, l3_employee_name = l3.employee_name
, l3_employee_level = l3.level_no

FROM employee l1

LEFT JOIN employee l2
ON l1.employee_id = l2.manager_id

LEFT JOIN employee l3
ON l2.employee_id = l3.manager_id

WHERE ISNULL(l1.level_no, 1) = 1
AND ISNULL(l2.level_no, 2) = 2
AND ISNULL(l3.level_no, 3) = 3</CommandText>
<DataSourceName>ds_master</DataSourceName>
</Query>
<Fields>
<Field Name="l1_employee_id">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l1_employee_id</DataField>
</Field>
<Field Name="l1_employee_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>l1_employee_name</DataField>
</Field>
<Field Name="l1_employee_level">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l1_employee_level</DataField>
</Field>
<Field Name="l2_employee_id">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l2_employee_id</DataField>
</Field>
<Field Name="l2_employee_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>l2_employee_name</DataField>
</Field>
<Field Name="l2_employee_level">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l2_employee_level</DataField>
</Field>
<Field Name="l3_employee_id">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l3_employee_id</DataField>
</Field>
<Field Name="l3_employee_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>l3_employee_name</DataField>
</Field>
<Field Name="l3_employee_level">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l3_employee_level</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>22.85714cm</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>2.5cm</TopMargin>
<PageHeight>29.7cm</PageHeight>
</Report>

[/code]