熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL Server >> 正文

如何獲得SQL Server 2000中知道對象的權限

2013-11-15 14:37:47  來源: SQL Server 

  問題如何獲得SQL SERVER 中知道對象的權限?
  解決
  前幾天看到有人問是否可以方便的獲得SQL SERVER指定對象的權限和指定USER的權
  限我寫了一個存儲過程可以獲得用戶和角色的權限請大家幫忙測試一下看看是
  否還有BUG:)
  
  IF OBJECTPROPERTY( OBJECT_ID( usp_getObjectAuthor ) IsProcedure ) =
  DROP PROC usp_getObjectAuthor
  GO
  /*************************************************************/
  /* Created By : leimin */
  /* Created On : May */
  /* Description : This stored procedure returns the object permission which
  you */
  /* GRANTDENY 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=
  Set @rowcount=
  
  /**************************************************************/
  /* 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(aid) as objectname
  user_name(auid) as usename
  case bissqlrole when then Group
  else User
  end as Role
  case aprotecttype when then Grant
  when then Grant
  when then Deny
  else Revoke
  end as ProtectType
  case a[action] when then REFERENCES
  when then CREATE FUNCTION
  when then SELECT
  when then INSERT
  when then DELETE
  when then UPDATE
  when then CREATE TABLE
  when then CREATE DATABASE
  when then CREATE VIEW
  when then CREATE PROCEDURE
  when then EXECUTE
  when then BACKUP DATABASE
  when then CREATE DEFAULT
  when then BACKUP LOG
  when then CREATE RULE
  else
  end as [Action]
  user_name(agrantor) as Grantor
  from sysprotects a inner join sysusers b on auid=buid
  where exists (select from sysobjects
  where [name]=object_name(aid) and xtype <>S )
  order by object_name(aid)
  
  select @rowcount=@@rowcount
  if @rowcount=
  begin
  select @rc=
  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= and @username is not null and @objectname is null
  begin
  if not exists(select * from sysusers where [uid]=user_id(@username) and
  status<>)
  begin
  select @rc=
  print The user name is not include in sysusers table
  return @rc
  end
  
  if exists(select from sysmembers where [memberuid]=user_id(@username))
  begin
  select object_name(aid) as objectname
  user_name(auid) as usename
  case bissqlrole when then Group
  else User
  end as Role
  case aprotecttype when then Grant
  when then Grant
  when then Deny
  else Revoke
  end as ProtectType
  case a[action] when then REFERENCES
  when then CREATE FUNCTION
  when then SELECT
  when then INSERT
  when then DELETE
  when then UPDATE
  when then CREATE TABLE
  when then CREATE DATABASE
  when then CREATE VIEW
  when then CREATE PROCEDURE
  when then EXECUTE
  when then BACKUP DATABASE
  when then CREATE DEFAULT
  when then BACKUP LOG
  when then CREATE RULE
  else
  end as [Action]
  user_name(agrantor) as Grantor
  from sysprotects a inner join sysusers b on auid=buid
  where exists (select from sysobjects
  where [name]=object_name(aid) and xtype <>S )
  and ( exists (select from sysmembers
  where groupuid=auid and memberuid=user_id(@username))
  or auid=user_id(@username))
  order by object_name(aid)
  
  select @rowcount=@@rowcount
  if @rowcount=
  begin
  select @rc=
  print @username+ have not any objects authorization
  return @rc
  end
  end
  else
  begin
  select object_name(aid) as objectname
  user_name(auid) as usename
  case bissqlrole when then Group
  else User
  end as Role
  case aprotecttype when then Grant
  when then Grant
  when then Deny
  else Revoke
  end as ProtectType
  case a[action] when then REFERENCES
  when then CREATE FUNCTION
  when then SELECT
  when then INSERT
  when then DELETE
  when then UPDATE
  when then CREATE TABLE
  when then CREATE DATABASE
  when then CREATE VIEW
  when then CREATE PROCEDURE
  when then EXECUTE
  when then BACKUP DATABASE
  when then CREATE DEFAULT
  when then BACKUP LOG
  when then CREATE RULE
  else
  end as [Action]
  user_name(agrantor) as Grantor
  from sysprotects a inner join sysusers b on auid=buid
  where exists (select from sysobjects
  where [name]=object_name(aid) and xtype <>S )
  and auid=user_id(@username)
  order by object_name(aid)
  
  select @rowcount=@@rowcount
  if @rowcount=
  begin
  select @rc=
  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= 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=
  return @rc
  end
  if @rc=
  begin
  select object_name(aid) as objectname
  user_name(auid) as usename
  case bissqlrole when then Group
  else User
  end as Role
  case aprotecttype when then Grant
  when then Grant
  when then Deny
  else Revoke
  end as ProtectType
  case a[action] when then REFERENCES
  when then CREATE FUNCTION
  when then SELECT
  when then INSERT
  when then DELETE
  when then UPDATE
  when then CREATE TABLE
  when then CREATE DATABASE
  when the
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22087.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.