当前位置导航:炫浪网>>网络学院>>编程开发>>MsSQL教程

如何获得SQL Server 2000中知道对象的权限


  问题:如何获得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'
  when 207 the
相关内容
赞助商链接