网络安全检测|网络安全服务|网络安全扫描-香港墨客投资移动版

　　问题:如何获得SQL SERVER 2000中知道对象的权限?

　　解决:

　　前几天看到有人问是否可以方便的获得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'