p; 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上一页 [1] [2] [3] [4] 下一页 |
