IDC资讯中心
前几天看到有人问是否可以方便的获得sql server2000指定对象的权限和指定user的权
限。我写了一个存储过程，可以获得用户和角色的权限。请大家帮忙测试一下。看看是
否还有bug:-)
if objectproperty( object_id( usp_getobjectauthor ) , isprocedure ) =1
drop proc usp_getobjectauthor
go
/***************************************************************************
*****/
/* created by : leimin */
/* created on : 29 may 2004 */
/* description : this stored procedure returns the object permission which
you */
/* grant,deny 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=0
set @rowcount=0
/***************************************************************************
*****/
/* 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(a.id) as objectname,
user_name(a.uid) as usename,
case b.issqlrole when 1 then group
else user
end as role,
case a.protecttype when 205 then grant
when 204 then grant
when 206 then deny
else revoke
end as protecttype,
case a.[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
else 0
end as [action],
user_name(a.grantor) as grantor
from sysprotects a inner join sysusers b on a.uid=b.uid
where exists (select 1 from sysobjects
where [name]=object_name(a.id) and xtype <>s )
order by object_name(a.id)
select @rowcount=@@rowcount
if @rowcount=0
begin
select @rc=-1
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=0 and @username is not null and @objectname is null
begin
if not exists(select * from sysusers where [uid]=user_id(@username) and
status<>0)
begin
select @rc=-2
print the user name is not include in sysusers table.
return @rc
end
if exists(select 1 from sysmembers where [memberuid]=user_id(@username))
begin
select object_name(a.id) as objectname,
user_name(a.uid) as usename,
case b.issqlrole when 1 then group
else user
end as role,
case a.protecttype when 205 then grant
when 204 then grant
when 206 then deny
else revoke
end as protecttype,
case a.[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
else 0
end as [action],
user_name(a.grantor) as grantor
from sysprotects a inner join sysusers b on a.uid=b.uid
where exists (select 1 from sysobjects
where [name]=object_name(a.id) and xtype <>s )
and ( exists (select 1 from sysmembers
where groupuid=a.uid and memberuid=user_id(@username))
or a.uid=user_id(@username))
order by object_name(a.id)
select @rowcount=@@rowcount
if @rowcount=0
begin
select @rc=-3
print @username+ have not any objects authorization.
return @rc
end
end
else
begin
select object_name(a.id) as objectname,
user_name(a.uid) as usename,
case b.issqlrole when 1 then group
else user
end as role,
case a.protecttype when 205 then grant
when 204 then grant
when 206 then deny
else revoke
end as protecttype,
case a.[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
else 0
end as [action],
user_name(a.grantor) as grantor
from sysprotects a inner join sysusers b on a.uid=b.uid
where exists (select 1 from sysobjects
where [name]=object_name(a.id) and xtype <>s )
and a.uid=user_id(@username)
order by object_name(a.id)
select @rowcount=@@rowcount
if @rowcount=0
begin
select @rc=-4
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=0 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=-5
return @rc
end
if @rc=0
begin
select object_name(a.id) as objectname,
user_name(a.uid) as usename,
case b.issqlrole when 1 then group
else user
end as role,
case a.protecttype when 205 then grant
when 204 then grant
when 206 then deny
else revoke
end as protecttype,
case a.[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
else 0
end as [action],
user_name(a.grantor) as grantor
from sysprotects a inner join sysusers b on a.uid=b.uid
where exists (select 1 from sysobjects
where [name]=object_name(a.id) and xtype <>s )
and [id]=object_id(@objectname)
order by object_name(a.id)
select @rowcount=@@rowcount
if @rowcount=0
begin
select @rc=-6
print @objectname+ have not grant authorization to any user
return @rc
end
end
end
/***************************************************************************
*****/
/* judge the input parameters ,if @objectname is not null and @username is
not null */
/* then return one objects authorization by one user */
/***************************************************************************
*****/
if @rc=0 and @objectname is not null and @username is not null
begin
if not exists(select * from sysobjects where [id]=object_id(@objectname)
and xtype<>s)
begin
select @rc=-7
print the object name is not include in sysobjects table.
return @rc
end
if not exists(select * from sysusers where [uid]=user_id(@username) and
status<>0)
begin
select @rc=-8
print the user name is not include in sysusers table.
return @rc
end
if exists(select 1 from sysmembers where [memberuid]=user_id(@username))
begin
select object_name(a.id) as objectname,
user_name(a.uid) as usename,
case b.issqlrole when 1 then group
else user
end as role,
case a.protecttype when 205 then grant
when 204 then grant
when 206 then deny
else revoke
end as protecttype,
case a.[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
else 0
end as [action],
user_name(a.grantor) as grantor
from sysprotects a inner join sysusers b on a.uid=b.uid
where exists (select 1 from sysobjects
where [name]=object_name(a.[id]) and xtype <>s )
and (exists (select 1 from sysmembers
where groupuid=a.uid and memberuid=user_id(@username))
or a.uid=user_id(@username))
and [id]=object_id(@objectname)
order by object_name(a.id)
select @rowcount=@@rowcount
if @rowcount=0
begin
select @rc=-9
print @username+ have not any objects authorization.
return @rc
end
end
else
begin
select object_name(a.id) as objectname,
user_name(a.uid) as usename,
case b.issqlrole when 1 then group
else user
end as role,
case a.protecttype when 205 then grant
when 204 then grant
when 206 then deny
else revoke
end as protecttype,
case a.[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
else 0
end as [action],
user_name(a.grantor) as grantor
from sysprotects a inner join sysusers b on a.uid=b.uid
where exists (select 1 from sysobjects
where [name]=object_name(a.[id]) and xtype <>s )
and a.uid=user_id(@username)
and [id]=object_id(@objectname)
order by object_name(a.id)
select @rowcount=@@rowcount
if @rowcount=0
begin
select @rc=-10
print @username+ have not any objects authorization.
return @rc
end
end
end
end
go
exec usp_getobjectauthor
