REVERT权限切换的操作步骤和注意事项

首先&＃xff0c;我们登录到SQL Server.其中&＃xff0c;有一个是具有很小权限的普通用户&＃xff0c;另一个是系统管理员角色中的一个成员。

USE master
GO
--Add Windows logins to SQL Server
IF NOT EXISTS (SELECT * FROM sys.syslogins WHERE name &＃61; &＃39;PC-201102041156\qanholas&＃39;)
CREATE LOGIN [PC-201102041156\qanholas]
FROM WINDOWS
WITH DEFAULT_DATABASE &＃61; qanholas
IF NOT EXISTS (SELECT * FROM sys.syslogins WHERE name &＃61; &＃39;PC-201102041156\DBA&＃39;)
CREATE LOGIN [PC-201102041156\DBA]
FROM WINDOWS
WITH DEFAULT_DATABASE &＃61; qanholas



USE qanholas
--Add the new logins to the AdventureWorks database
CREATE USER qanholas FOR LOGIN [PC-201102041156\qanholas]
CREATE USER dba FOR LOGIN [PC-201102041156\DBA]
--Add SQLDBA Windows account to the db_owner role
EXEC sp_addrolemember &＃39;db_owner&＃39;, &＃39;dba&＃39;
GO




--Create procedure that executes a SELECT with a BACKUP DATABASE command
CREATE PROCEDURE dbo.DisplayContextwithRevert
WITH EXECUTE AS CALLER
AS
--The user will only be granted permission to do this section of the code
SELECT * FROM ip
--We will just display the execution context of the user executing this section of the code for demonstration
SELECT CURRENT_USER AS UserName;
--We will switch execution context to a more privileged user to do this portion of the code
EXECUTE AS USER&＃61;&＃39;dba&＃39;;
BACKUP DATABASE qanholas TO DISK&＃61;&＃39;C:\qanholas.BAK&＃39; WITH INIT, STATS&＃61;10;
--We will just display the execution context of the user executing this section of the code
SELECT CURRENT_USER AS UserName;
--We will revert to the execution context of the original caller to limit the privileges back
REVERT;
SELECT * FROM ip
SELECT CURRENT_USER AS UserName;
GO



USE qanholas;


GRANT EXECUTE ON dbo.DisplayContextwithRevert TO qanholas
GRANT SELECT ON ip TO qanholas
GO
-- Grant the IMPERSONATE permission on the SQLUser1 user so it can switch execution context to SQLDBA
GRANT IMPERSONATE ON USER:: dba TO qanholas