|
|
 |
专题栏目 |
 |
|
相关文章 |
|
|
|
|
|
 |
如何获得SQL SERVER2000数据库指定对象的权限列表？ |
热 |
|
| 如何获得SQL SERVER2000数据库指定对象的权限列表？ |
|
作者：leimin 文章来源：不详 点击数： 更新时间：2007-11-14 11:06:07  |
|
''''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] [2] [3] [4] 下一页 |
|
| 文章录入：mintao 责任编辑：mintao |
|
|
上一篇文章： 使用T-SQL导入多个文件数据到SQL Server中
下一篇文章： 使用xml生成查询用sql语句 |
|
|
| 【字体：小 大】【发表评论】【加入收藏】【告诉好友】【打印此文】【关闭窗口】 |
|
 注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] |
| 网友评论：（只显示最新10条。评论内容只代表网友观点，与本站立场无关！） |
| |
|
|
|
|
