Seperating the results of sp_dbfixedrolepermission by fixedbroles

[Tyler Smith Watu]

I will like to seperate the contents of sp_dbfixedrolep ermission
into groups by the fixedrole (With the LUXURY of a heading).Is there
a any short way or straight forward command to do that?

TIA

TY

[Erland Sommarskog]

Re: Seperating the results of sp_dbfixedrolep ermission by fixedbroles

[posted and mailed, please reply in news]

Tyler Smith Watu (watuni2000@yah oo.co.nz) writes:[color=blue]
> I will like to seperate the contents of sp_dbfixedrolep ermission
> into groups by the fixedrole (With the LUXURY of a heading).Is there
> a any short way or straight forward command to do that?[/color]

Normally you would use a reporting tool for things like adding headers.
But assuming that you want to run this from Query Analyzer, yes there
is a way, but straightforward ? Nah...

Here is the beast:

SELECT * FROM
(SELECT * FROM
OPENQUERY (LOOPBACK,
'SET FMTONLY OFF EXEC tempdb..sp_dbfi xedrolepermissi on'))
AS h (DbFixedRole, Permission)
ORDER BY DbFixedRole
COMPUTE COUNT(Permissio n) BY DbFixedRole

The non-relational COMPUTE is the one way there is to get a header
for each group in a header. An alternative though, would be to get
data into temp table with a third column, which is char(13) + char(10)
for the last row in each group.

To run a system stored procedure in a SELECT statement I use OPENQUERY.
LOOPBACK is a linked server that points back to the local server. The
SET FMTONLY OFF was necessary, because apparently sp_dbfixedrolep ermission
uses temp tables. The FMTONLY OFF fools OPENQUERY, but the procedure
is actually ran twice. While this method can be used to run about any
stored procedure from a SELECT statement, it should be used with caution.
See http://www.sommarskog.se/share_data.html#OPENQUERY for more details.

I also needed a derived table, so I could specify the column names.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Tyler Smith Watu]

Re: Seperating the results of sp_dbfixedrolep ermission by fixedbroles

Before I get some dust off my books did you say there was an
alternative way?
This may be asking too much but If you do not mind could you elaborate
a little on the alternative way?

Thank you very much for your time

TY

[Erland Sommarskog]

Re: Seperating the results of sp_dbfixedrolep ermission by fixedbroles

Tyler Smith Watu (watuni2000@yah oo.co.nz) writes:[color=blue]
> Before I get some dust off my books did you say there was an
> alternative way?
> This may be asking too much but If you do not mind could you elaborate
> a little on the alternative way?[/color]

It's even uglier:

SELECT *, last = ' '
INTO #tmp
FROM
(SELECT * FROM
OPENQUERY (KESÄMETSÄ,
'SET FMTONLY OFF EXEC tempdb..sp_dbfi xedrolepermissi on'))
AS h (DbFixedRole, Permission)
ORDER BY DbFixedRole, Permission

UPDATE #tmp
SET last = char(10)
FROM #tmp t
JOIN (SELECT DbFixedRole, perm = MAX(Permission)
FROM #tmp
GROUP BY DbFixedRole) u
ON t.DbFixedRole = u.DbFixedRole
AND t.Permission = u.perm

SELECT * FROM #tmp ORDER BY DbFixedRole, Permission


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Tyler Smith Watu]

Re: Seperating the results of sp_dbfixedrolep ermission by fixedbroles

Erland Sommarskog <esquel@sommars kog.se> wrote in message news:<Xns9563ED A0EFB18Yazorman @127.0.0.1>...[color=blue]
> Tyler Smith Watu (watuni2000@yah oo.co.nz) writes:[color=green]
> > Before I get some dust off my books did you say there was an
> > alternative way?
> > This may be asking too much but If you do not mind could you elaborate
> > a little on the alternative way?[/color]
>
> It's even uglier:
>
> SELECT *, last = ' '
> INTO #tmp
> FROM
> (SELECT * FROM
> OPENQUERY (KESÄMETSÄ,
> 'SET FMTONLY OFF EXEC tempdb..sp_dbfi xedrolepermissi on'))
> AS h (DbFixedRole, Permission)
> ORDER BY DbFixedRole, Permission
>
> UPDATE #tmp
> SET last = char(10)
> FROM #tmp t
> JOIN (SELECT DbFixedRole, perm = MAX(Permission)
> FROM #tmp
> GROUP BY DbFixedRole) u
> ON t.DbFixedRole = u.DbFixedRole
> AND t.Permission = u.perm
>
> SELECT * FROM #tmp ORDER BY DbFixedRole, Permission[/color]

Thanks for the opportunity to learn something new.

After setting up a linked server and running the transact sql provided
the results are about identical to what I had

1)I created two tables and inserted the values of
sp_dbfixedrolep ermission
on one and sp_helpsrvrolem ember on the other .
2)The second table had an identity column which was used to ensure I
could get the roles for each fixeddbrole as needed.
3)The two tables where then joined using a stored procedure which
produced the required results.

Now the not so necessary question is

Is there some configuration to clear the tabs in the resultset?i.e So
that it is possible to cut and paste ALL the resultset in notepad
(not piecemeal)



Thanks

TY

[Erland Sommarskog]

Re: Seperating the results of sp_dbfixedrolep ermission by fixedbroles

Tyler Smith Watu (watuni2000@yah oo.co.nz) writes:[color=blue]
> Is there some configuration to clear the tabs in the resultset?i.e So
> that it is possible to cut and paste ALL the resultset in notepad
> (not piecemeal)[/color]

I'm not sure what you mean here. The result set itself does not include
any formatting. If you run from text mode in Query Analyzer, all you
will get is spaces. Many spaces that's true, and maybe too many spaces.

But you could get the data into a temp table, and the use dynamic SQL to
select a result set where the column widths are adapted to the largest
rows.

Rather than giving an example on how to do it, I refer you to the source
code of the system stored procedure sp_who2 that employs this technique.
Press F8 to get an object browser, and then drill down to it in the master
database.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp