做权限管理的时候，对某个用户的权限进行检索。
--用户继承树
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