July 11, 2008 at 8:35 am
I posted this similar a few days ago and simply wanted to restate my problem. I want to take two querys and post the results in the same line. I want to list all orphaned users in a database in the first column and the # of objects that ID owns in the second (similar to below).
------------------------------------------------------------------------------
.OrphanedUser. Owned_Objs
User123 33
DBO 345
------------------------------------------------------------------------------
SELECT
CAST(name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User], '' Owned_Objs
FROM sysusers su
WHERE su.islogin = 1
AND su.name <> 'guest'
AND NOT EXISTS
(
SELECT 1
FROM master..sysxlogins sl
WHERE su.sid = sl.sid)
----------------------------------------------------------------------------
select count(a.name) from sysobjects a, sysusers b
where a.uid = b.uid
and b.name = 'DBO'
July 11, 2008 at 8:51 am
How about something like this.
WITH UserObjects
AS
(
select count(*) NbrOfObjects, b.name
from sysobjects a,
sysusers b
where a.uid = b.uid
group by b.name
)
SELECT
CAST(su.name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User],
NbrOfObjects
FROM sysusers su JOIN
UserObjects uo ON
su.name = uo.name
WHERE su.islogin = 1
AND su.name <> 'guest'
AND NOT EXISTS
(
SELECT 1
FROM master..syslogins sl
WHERE su.sid = sl.sid)
July 11, 2008 at 8:55 am
Silly question of the day (and I don't want to use it this early). How would this wrk on 2000..?
July 11, 2008 at 9:08 am
You can replace the CTE with a derived table.
SELECT
CAST(su.name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User],
NbrOfObjects
FROM sysusers su JOIN
(select count(*) NbrOfObjects, b.name
from sysobjects a,
sysusers b
where a.uid = b.uid
group by b.name) uo ON
su.name = uo.name
WHERE su.islogin = 1
AND su.name <> 'guest'
AND NOT EXISTS
(
SELECT 1
FROM master..syslogins sl
WHERE su.sid = sl.sid)
July 11, 2008 at 9:58 am
Thank you very much. I was having issues combining these two...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply