如何獲得SQL Server 2000中知道對象的權限

　　問題如何獲得SQL SERVER 中知道對象的權限？
　　解決
　　前幾天看到有人問是否可以方便的獲得SQL SERVER指定對象的權限和指定USER的權
　　限我寫了一個存儲過程可以獲得用戶和角色的權限請大家幫忙測試一下看看是
　　否還有BUG:)
　　
　　IF OBJECTPROPERTY( OBJECT_ID( usp_getObjectAuthor ) IsProcedure ) =
　　DROP PROC usp_getObjectAuthor
　　GO
　　/*************************************************************/
　　/* Created By : leimin */
　　/* Created On : May */
　　/* Description : This stored procedure returns the object permission which
　　you */
　　/* GRANTDENY and REVOKE
　　*/
　　/**************************************************************/
　　Create proc usp_getObjectAuthor
　　@objectname sysname = null
　　@username sysname = null
　　as
　　set nocount on
　　begin
　　/**************************************************************/
　　/* defined the initilization variable */
　　/**************************************************************/
　　Declare @rc int
　　Declare @rowcount int
　　Declare @groupid int
　　
　　Set @rc=
　　Set @rowcount=
　　
　　/**************************************************************/
　　/* Judge the input parameters if @objectname is null and @username is
　　null */
　　/* then return all objects authorization */
　　/*************************************************************/
　　if @objectname is null and @username is null
　　begin
　　select object_name(aid) as objectname
　　user_name(auid) as usename
　　case bissqlrole when then Group
　　else User
　　end as Role
　　case aprotecttype when then Grant
　　when then Grant
　　when then Deny
　　else Revoke
　　end as ProtectType
　　case a[action] when then REFERENCES
　　when then CREATE FUNCTION
　　when then SELECT
　　when then INSERT
　　when then DELETE
　　when then UPDATE
　　when then CREATE TABLE
　　when then CREATE DATABASE
　　when then CREATE VIEW
　　when then CREATE PROCEDURE
　　when then EXECUTE
　　when then BACKUP DATABASE
　　when then CREATE DEFAULT
　　when then BACKUP LOG
　　when then CREATE RULE
　　else
　　end as [Action]
　　user_name(agrantor) as Grantor
　　from sysprotects a inner join sysusers b on auid=buid
　　where exists (select from sysobjects
　　where [name]=object_name(aid) and xtype <>S )
　　order by object_name(aid)
　　
　　select @rowcount=@@rowcount
　　if @rowcount=
　　begin
　　select @rc=
　　print There a no user objects in database!
　　return @rc
　　end
　　end
　　/**************************************************************/
　　/* Judge the input parameters if @objectname is null and @username is not
　　null */
　　/* then return all objects authorization where relation @username */
　　/* if the user belong to a group so we must add the group authorization */
　　/**************************************************************/
　　if @rc= and @username is not null and @objectname is null
　　begin
　　if not exists(select * from sysusers where [uid]=user_id(@username) and
　　status<>)
　　begin
　　select @rc=
　　print The user name is not include in sysusers table
　　return @rc
　　end
　　
　　if exists(select from sysmembers where [memberuid]=user_id(@username))
　　begin
　　select object_name(aid) as objectname
　　user_name(auid) as usename
　　case bissqlrole when then Group
　　else User
　　end as Role
　　case aprotecttype when then Grant
　　when then Grant
　　when then Deny
　　else Revoke
　　end as ProtectType
　　case a[action] when then REFERENCES
　　when then CREATE FUNCTION
　　when then SELECT
　　when then INSERT
　　when then DELETE
　　when then UPDATE
　　when then CREATE TABLE
　　when then CREATE DATABASE
　　when then CREATE VIEW
　　when then CREATE PROCEDURE
　　when then EXECUTE
　　when then BACKUP DATABASE
　　when then CREATE DEFAULT
　　when then BACKUP LOG
　　when then CREATE RULE
　　else
　　end as [Action]
　　user_name(agrantor) as Grantor
　　from sysprotects a inner join sysusers b on auid=buid
　　where exists (select from sysobjects
　　where [name]=object_name(aid) and xtype <>S )
　　and ( exists (select from sysmembers
　　where groupuid=auid and memberuid=user_id(@username))
　　or auid=user_id(@username))
　　order by object_name(aid)
　　
　　select @rowcount=@@rowcount
　　if @rowcount=
　　begin
　　select @rc=
　　print @username+ have not any objects authorization
　　return @rc
　　end
　　end
　　else
　　begin
　　select object_name(aid) as objectname
　　user_name(auid) as usename
　　case bissqlrole when then Group
　　else User
　　end as Role
　　case aprotecttype when then Grant
　　when then Grant
　　when then Deny
　　else Revoke
　　end as ProtectType
　　case a[action] when then REFERENCES
　　when then CREATE FUNCTION
　　when then SELECT
　　when then INSERT
　　when then DELETE
　　when then UPDATE
　　when then CREATE TABLE
　　when then CREATE DATABASE
　　when then CREATE VIEW
　　when then CREATE PROCEDURE
　　when then EXECUTE
　　when then BACKUP DATABASE
　　when then CREATE DEFAULT
　　when then BACKUP LOG
　　when then CREATE RULE
　　else
　　end as [Action]
　　user_name(agrantor) as Grantor
　　from sysprotects a inner join sysusers b on auid=buid
　　where exists (select from sysobjects
　　where [name]=object_name(aid) and xtype <>S )
　　and auid=user_id(@username)
　　order by object_name(aid)
　　
　　select @rowcount=@@rowcount
　　if @rowcount=
　　begin
　　select @rc=
　　print @username+ have not any objects authorization
　　return @rc
　　end
　　end
　　
　　end
　　/**************************************************************/
　　/* Judge the input parameters if @objectname is not null and @username is
　　null */
　　/* then return one objects authorization */
　　/**************************************************************/
　　if @rc= and @objectname is not null and @username is null
　　begin
　　if not exists(select * from sysobjects where [id]=object_id(@objectname)
　　and xtype<>S)
　　begin
　　select @rc=
　　return @rc
　　end
　　if @rc=
　　begin
　　select object_name(aid) as objectname
　　user_name(auid) as usename
　　case bissqlrole when then Group
　　else User
　　end as Role
　　case aprotecttype when then Grant
　　when then Grant
　　when then Deny
　　else Revoke
　　end as ProtectType
　　case a[action] when then REFERENCES
　　when then CREATE FUNCTION
　　when then SELECT
　　when then INSERT
　　when then DELETE
　　when then UPDATE
　　when then CREATE TABLE
　　when then CREATE DATABASE
　　when the
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22087.html