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

SQL Server如何得到用户的继承列表


  在实际开发中,尤其是在做权限管理的时候,常常要对对某个用户的权限进行检索,本文给出SQL Server中的一个实例.
  
  --用户继承树
  CREATE function getUserTree(@UserName sysname, --用户名
  @Seq bit   --查找方式:0查找子孙 1.查找祖先
  )
  returns @Result table(UserID sysname,UserName sysname,Level int)
  as
  begin
  declare @UserId sysname
  set @userId=user_id(@userName)
  
  if @userid is null
  begin
  --raiserror('指定的用户名不存在',16,1)
  return
  end
  
  DECLARE @level int, @line char(20)
  declare @stack table(item sysname, level int)
  INSERT INTO @stack VALUES (@UserID, 1)
  SELECT @level = 1
  
  WHILE @level > 0
  BEGIN
  IF EXISTS (SELECT * FROM @stack WHERE level = @level)
  BEGIN
  SELECT @userId = item
  FROM @stack
  WHERE level = @level
  insert into @Result values(@UserId,User_name(@userID),@level)
  
  DELETE FROM @stack
  WHERE level = @level
  AND item = @userId
  
  if @Seq=1 --查找祖先
  INSERT @stack
  SELECT groupuid, @level + 1
  FROM sysmembers
  WHERE memberuid = @userId
  else --查找子孙
  INSERT @stack
  SELECT memberuid, @level + 1
  FROM sysmembers
  WHERE groupuid = @userId
  
  IF @@ROWCOUNT > 0
  SELECT @level = @level + 1
  END
  ELSE
  SELECT @level = @level - 1
  END -- WHILE
  
  return
  end
  
  实例:
  exec sp_addrole 'Users'
  exec sp_addrole 'BusinessMan'
  exec sp_addrolemember 'Users','BusinessMan'
  exec sp_addrole 'Saler'
  exec sp_addrolemember 'BusinessMan','Saler'
  
  exec sp_addlogin 'OrderMan','OrderMan','lifeng'
  exec sp_addrolemember 'Saler','OrderMan'
  exec sp_grantdbaccess 'OrderMan','OrderMan'
  select * from getUserTree('OrderMan',1)
  
  结果显示
  UserID  USRENAME   Level
  5       OrderMan     1
  16402   Saler         2
  16401   BusinessMan 3
  16403   Users        4
  
  这种方法,也在MRP/ERP系统中遍历BOM时使用
相关内容
赞助商链接