在實際開發中，尤其是在做權限管理的時候，常常要對對某個用戶的權限進行檢索，本文給出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時使用。