查某個特定 User 名對各 DB 內物件的權限

2008/2005

例如:public 這個 User 在各 DB 內對物件的權限

EXECUTE master.sys.sp_MSforeachdb ‘USE [?]; select “?" DBName,USERS.name,o.name, * from  sys.database_permissions prmssn
INNER JOIN sys.database_principals AS grantee ON grantee.principal_id = prmssn.grantee_principal_id
JOIN sys.sysusers USERS ON USERS.sid=grantee.sid
join sys.objects o on major_id=o.object_id
where USERS.name="public"‘

2000

select u.name UserName,object_name(p.id) ObjectName,
Case p.Action
WHEN 26 THEN ‘REFERENCES’
WHEN 178 THEN ‘ CREATE FUNCTION’
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 ‘ BACKUP DATABASE’
WHEN 233 THEN ‘ CREATE DEFAULT’
WHEN 235 THEN ‘ BACKUP LOG’
WHEN 236 THEN ‘ CREATE RULE’
END [Action],
Case p.protecttype
WHEN 204 THEN ‘GRANT_W_GRANT’
WHEN 205 THEN ‘GRANT’
WHEN 206 THEN ‘DENY’
END ProtectType,
* from sysprotects p join sysusers u on p.uid=u.uid

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: