问题如何获得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
