SQL HELP

[Vincento Harris]

I am trying to order user roles by privileges
Would ideally like it to look like this
severole permission
*******************************
sysadmin Kill
restore database
restore log
********************************
instead of
severrole premission
********************************
sysadmin restore database
sysadmin restore log
sysadmin kill
********************************

In oracle break on serverole normally does it ..
BTW your recommendations for Programming in SQL server book(s)
are welcome.(Not very advanced ones)

I appreciate all the help from the google news group

Vince

[Erland Sommarskog]

[posted and mailed, please reply in news]

Vincento Harris (wu*****@yahoo.com) writes:

I am trying to order user roles by privileges
Would ideally like it to look like this
severole permission
*******************************
sysadmin Kill
restore database
restore log
********************************
instead of
severrole premission
********************************
sysadmin restore database
sysadmin restore log
sysadmin kill
********************************

In oracle break on serverole normally does it ..

This is thing that is best left to a reporting tool. But you can
achieve this in SQL, although it is a bit messy.

CREATE TABLE #tmp (tmpid int IDENTITY,
serverrole varchar(20) NOT NULL,
permission varchar(20) NOT NULL)

INSERT #tmp (serverrole, permission)
SELECT serverrole, permission
FROM tbl
ORDER BY serverrole, permission
OPTION (MAXDOP 1)

UPDATE a
SET serverrole = ' '
FROM #tmp a
JOIN #tmp b ON a.tmpid = b.tmpid - 1
AND a.serverrole = b.serverrole

SELECT serverrole, permission
FROM #tmp
ORDER BY tmpid

The above is untested. You may to play around a little to get it
working.

The OPTION (MAXDOP 1) is there to prevent parallellism, as parallellism
can cause the identity values not to be the expected ones.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp