권한조회 SQL

select name, owner = user_name(uid), crdate, objtype = sysstat & 0xf, id, deltrig from dbo.sysobjects o where power(2, sysstat & 0xf) & 31 != 0 and not (OBJECTPROPERTY(id, N'IsDefaultCnst') = 1 and category & 0x0800 != 0) and o.name not like N'#%' order by name, owner

select a = o.name, b = user_name(o.uid), user_name(p.uid), o.sysstat & 0xf, p.id, action, protecttype from dbo.sysprotects p, dbo.sysobjects o, master.dbo.spt_values a where o.id = p.id and (( p.action in (193, 197) and ((p.columns & 1) = 1) ) or ( p.action in (195, 196, 224, 26) )) and (convert(tinyint, substring( isnull(p.columns, 0x01), a.low, 1)) & a.high != 0) and a.type = N'P' and a.number = 0 and p.uid = 0 order by a, b

user_name 함수는 uid를 받아서 user_name으로 표시해준다.

select * from sys.database_role_members as a join sys.sysusers as b ona.role_principal_id = b.uid or a.member_principal_id = b.uid

현재 데이터베이스 유저들의 권한조회