在实际开发中，尤其是在做权限管理的时候，常常要对对某个用户的权限进行检索，本文给出SQL Server中的一个实例。

--用户继承树

CREATE functi>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时使用。

无忧站长学院，建站自然无忧www.5ucms.org