为了记录我正在开发的应用程序的使用情况,我需要每个使用我的应用程序的用户都使用自己的凭据对我的SQL Server数据库执行查询。
为了不以可检索的方式存储他们的密码,我不能在每个用户的基础上创建连接(因为这需要在他们登录时通过简短的窗口知道他们的密码(。
这个问题看似显而易见的解决方案(可能是次优的(是以通用"应用程序"用户身份运行所有敏感查询,模拟登录用户(要求我只将登录用户与用户名相关联......这不是一件坏事(。
我的问题是,我不确定如何向某个角色的所有用户或一般所有用户授予模拟权限(这不是最聪明的主意,因为我不希望应用程序模拟系统管理员,例如(。
grant impersonate on all to ApplicationLogin
不起作用,而且我找不到任何文档表明对角色成员授予模拟是可行的......
有什么想法吗?
您可以使用动态 sql 。下面的代码提取与特定角色相关的所有用户,然后授予对用户模拟的权限。您应该在应用程序登录时创建一个用户以将其与数据库相关联,然后授予对特定角色的所有成员进行模拟的权限。这是代码:
CREATE TRIGGER S2
ON DATABASE
FOR CREATE_USER
AS
CREATE TABLE #T
(PRINCIPAL_NAME NVARCHAR(100),ROLE_NAME NVARCHAR(100));
WITH L AS (SELECT *
FROM (SELECT P.name AS 'PRINCIPAL_NAME',R.role_principal_id AS 'GH'
FROM SYS.database_principals P,sys.database_role_members R
WHERE P.principal_id=R.member_principal_id OR P.principal_id=R.role_principal_id
AND type<>'R') S INNER JOIN (SELECT P.name AS 'ROLE_NAME',P.principal_id AS 'GHA'
FROM SYS.database_principals P,sys.database_role_members R
WHERE P.principal_id=R.member_principal_id OR P.principal_id=R.role_principal_id
AND type='R') D
ON D.GHA=S.GH)
INSERT INTO #T
SELECT DISTINCT PRINCIPAL_NAME,ROLE_NAME
FROM L
------------ ENTER ROLE NAME HERE
WHERE ROLE_NAME LIKE '%%'
------------
DECLARE @P NVARCHAR(100),@TEXT NVARCHAR(MAX)=''
------------------------- CHANGE IT TO YOUR DESIRED NAME OF YOUR APPLICATION USER
DECLARE @APPUSER NVARCHAR(100)='APPLICATION_USER'
-------------------------
DECLARE C CURSOR FOR SELECT PRINCIPAL_NAME FROM #T
OPEN C
FETCH NEXT FROM C INTO @P
WHILE(@@FETCH_STATUS=0)
BEGIN
SET @TEXT+='GRANT IMPERSONATE ON USER::['+@P+'] TO '+@APPUSER+' '
FETCH NEXT FROM C INTO @P
END
CLOSE C
DEALLOCATE C
DROP TABLE #T
EXEC(@TEXT)
我希望它对你有用。
可以通过存储过程创建用户。存储过程的最后一行是授予模拟。
若要设置当前用户,必须循环浏览所有用户,并设置一次授权模拟。