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时使用