取得權限資訊
1
2
3
4
sp_helprotect --顯示權限一覽表
sp_helprotect @username='username' --顯示使用者的權限
sp_table_privileges 'table_name' --顯示資料表的權限
sp_column_privileges 'table_name' --顯示資料表欄位的權限
範例
1
2
3
4
sp_helprotect --顯示權限一覽表
sp_helprotect @username='riva' --顯示使用者"riva"的權限
sp_table_privileges 'Persons' --顯示"Persons"資料表的權限
sp_column_privileges 'Persons' --顯示"Persons"資料表欄位的權限
執行結果：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--顯示使用者"riva"的權限
Owner Object Grantee Grantor ProtectType Action Column
. . riva dbo Grant CONNECT .
--顯示"Persons"資料表的權限
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME GRANTOR GRANTEE PRIVILEGE IS_GRANTABLE
SqlExercises dbo Persons dbo dbo DELETE YES
SqlExercises dbo Persons dbo dbo INSERT YES
SqlExercises dbo Persons dbo dbo REFERENCES YES
SqlExercises dbo Persons dbo dbo SELECT YES
SqlExercises dbo Persons dbo dbo UPDATE YES
--顯示"Persons"資料表欄位的權限
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME GRANTOR GRANTEE PRIVILEGE IS_GRANTABLE
SqlExercises dbo Persons Address dbo dbo INSERT YES
SqlExercises dbo Persons Address dbo dbo REFERENCES YES
SqlExercises dbo Persons Address dbo dbo SELECT YES
SqlExercises dbo Persons Address dbo dbo UPDATE YES
SqlExercises dbo Persons City dbo dbo INSERT YES