470,628 Members | 2,315 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,628 developers. It's quick & easy.

Seperating the results of sp_dbfixedrolepermission by fixedbroles

I will like to seperate the contents of sp_dbfixedrolepermission
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
Jul 20 '05 #1
5 1655
[posted and mailed, please reply in news]

Tyler Smith Watu (wa********@yahoo.co.nz) writes:
I will like to seperate the contents of sp_dbfixedrolepermission
into groups by the fixedrole (With the LUXURY of a heading).Is there
a any short way or straight forward command to do that?


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_dbfixedrolepermission'))
AS h (DbFixedRole, Permission)
ORDER BY DbFixedRole
COMPUTE COUNT(Permission) 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_dbfixedrolepermission
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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
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
Jul 20 '05 #3
Tyler Smith Watu (wa********@yahoo.co.nz) writes:
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?


It's even uglier:

SELECT *, last = ' '
INTO #tmp
FROM
(SELECT * FROM
OPENQUERY (KESÄMETSÄ,
'SET FMTONLY OFF EXEC tempdb..sp_dbfixedrolepermission'))
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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...
Tyler Smith Watu (wa********@yahoo.co.nz) writes:
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?


It's even uglier:

SELECT *, last = ' '
INTO #tmp
FROM
(SELECT * FROM
OPENQUERY (KESÄMETSÄ,
'SET FMTONLY OFF EXEC tempdb..sp_dbfixedrolepermission'))
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


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_dbfixedrolepermission
on one and sp_helpsrvrolemember 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
Jul 20 '05 #5
Tyler Smith Watu (wa********@yahoo.co.nz) writes:
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)


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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by flupke | last post: by
6 posts views Thread by Jo K. | last post: by
reply views Thread by AnkitAsDeveloper [Ankit] | last post: by
4 posts views Thread by jaYPee | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.