|
在实际开发中，尤其是在做权限管理的时候，常常要对对某个用户的权限进行检索，本文给出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时使用
| 
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 