sql权限查询
来源:互联网 发布:发票系统网络无法连接 编辑:程序博客网 时间:2024/06/03 15:58
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
- sql权限查询语句
- sql权限查询
- ORACLE EBS 权限查询SQL
- SQL根据员工权限查询素材
- sql 权限
- 【Sql Server学习】Sql Server数据查询、数据更新、视图、权限管理
- PostgreSQL的权限查询
- oracle 查询权限
- 查询oracle权限
- Oracle 查询权限角色
- 查询flashback_transaction_query没有权限
- android 权限查询汇总
- postgresql 表权限查询
- Oracle 权限查询
- Android权限查询
- Android查询:权限大全
- EAS权限查询
- flashback_transaction_query查询权限
- poj1226——Substrings//KMP
- sql权限查询语句
- 好玩的宏定义
- tcp/ip 三次握手
- lwIP raw API
- sql权限查询
- KBuild MakeFile介绍
- DropDownList与DataTable数据绑定的方法
- python字符串之符合字段名
- 在asp.Net中使用fileuplod控件实现上传图片的功能
- 现实世界的Windows Azure:采访Transparencia Sp. z o.o的Grzegorz Skowron-Moszkowicz
- Uploadify使用介绍
- ActiveX控件打包成Cab置于网页中自动下载安装
- android Spinner (like flex combox)