June 1, 2005 at 8:29 am
Is there an easy what to do the above. i want to drop a user who owns objects. BUT I need to discover what he owns.
Thanks
June 1, 2005 at 8:53 am
Select Name, XType from dbo.SysObjects O where USER_NAME (O.UID) = 'dbo' order by XType, Name
July 22, 2005 at 4:11 am
I wrote this A LONG time ago, you might find it useful. There might be a metadata object that has this info, but I'm not too up on what the metadata objects contain and don't know of one off-hand.
/* Microsoft SQL Server - Scripting*/
/* Server: SQLSERVER*/
/* Database: TADS*/
/* Creation Date 8/29/00 10:11:42 AM */
/****** Object: View dbo.vsysShowPermissions Script Date: 8/29/00 10:11:42 AM ******/
if exists (select * from sysobjects where id = object_id('dbo.vsysShowPermissions') and sysstat & 0xf = 2)
drop view dbo.vsysShowPermissions
GO
/****** Object: View dbo.vsysShowPermissions Script Date: 8/29/00 10:11:42 AM ******/
-- Create view vsysShowPermissions
-- as
select ObjName = convert(char(30), o.name),
ObjType =
CASE o.type
when 'C' then 'Constraint'
when 'D' then 'Default'
when 'F' then 'Foreign Key'
when 'K' then 'Primary Key'
when 'L' then 'Log'
when 'P' then 'Procedure'
when 'R' then 'Rule'
when 'RF' then 'Replication SP'
when 'S' then 'SYSTEM TABLE'
when 'TR' then 'Trigger'
when 'U' then 'Table'
when 'V' then 'View'
when 'X' then 'Extended SP'
ELSE '*BAD OBJECT TYPE'
END,
UserName = convert(char(20),
case
when p.id is null then ' (no users)'
when u.uid = u.gid then lower(u.name)
else upper(u.name)
end),
Permission = convert(char(20),
(CASE p.action
when 26 then 'References'
when 193 then 'Select'
when 195 then 'Insert'
when 196 then 'Delete'
when 197 then 'Update'
when 198 then 'Create Table'
when 203 then 'Create Database'
when 207 then 'Create View'
when 222 then 'Create Procedure'
when 224 then 'Execute'
when 228 then 'Dump Database'
when 233 then 'Create Default'
when 235 then 'Dump Transaction'
when 236 then 'Create Rule'
ELSE ' (no permissions)'
END
+
CASE p.protecttype
when 205 then '/Grant'
when 206 then '/REVOKE'
ELSE ''
END))
-- , p.id, p.uid, p.protecttype, p.action
from sysobjects o
left join sysprotects p on o.id = p.id
left join sysusers u on p.uid = u.uid
GO
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply