(New to SSIS. None of the similar questions mention this particular impersonation error.) I'm trying to run an SSIS package as an Agent job via a proxy, and I'm getting a non-helpful error message. The credential object is for an AD account. I'm able to log in with the password to other services, but attempting to run the Agent job errors out in the Execute job step. Running the job as SQL Agent service account worked, but it doesn't have access to the filesystem that the SSIS job is supposed to pull data from. I checked the agent log file and it's this: SQLServer Error: 22046, Impersonation error. [SQLSTATE 42000] I checked the Windows security log in Event Viewer: Failure Information: Failure Reason: Unknown user name or bad password. Status: 0xC000006D Sub Status: 0xC000006A I tried it with my (sysadmin) user ID and password in a different credential / proxy and got the same error. I know that account works. We granted both SQL Agent and this account user rights to logon as batch job, and the agent account permission to impersonate. We also tried making the various accounts Windows admins and sysadmins, but nothing has helped. This worked right out of the box in our QA domain. Any suggestions how to get this to work? What domain/local/SQL Server security settings would enable or prevent the agent impersonating a user? Windows 2016 + SQL Server 2016

Test Cube, Test User, connect to it(Azure Analysis Service) in SSMS, right click the cube, browse, click on "Impersonate": And you will see a series of prompts that are specific to a on-premise implementation of AD, so local users, local groups(In this case it's just my local system): How do I select a user from MS Entra through these prompts? If that can't been done is there another easy UI driven way to impersonate a user? Did Microsoft just not extend support for this authentication type to SSMS(at least for Azure Analysis Services)?

We've been moving application packages from the older methods of storage and execution into the SSIS Catalog. We're required to use an enterprise scheduling system instead of the SQL Server Agent to execute batch jobs and SSIS packages. Basically, the scheduler opens a command line on the server and executes Windows commands. In our case, we'd be using SQLCMD to execute a stored procedure like the one below. Here's what I need to be able to do: CREATE PROCEDURE usp_Execute_Packages @Package_name NVARCHAR(260) WITH EXECUTE AS 'Domain\User' AS DECLARE @execution_id bigint EXEC [SSISDB].[catalog].[create_execution] @package_name=@Package_name, @execution_id=@execution_id OUTPUT, @folder_name=N'A Folder Name', @project_name=N'Test Deployment' EXEC [SSISDB].[catalog].[start_execution] @execution_id GO But, this doesn't seem to work; I get an error telling me that I can't use a SQL Server account to start an execution. I assume it's having an issue with the EXECUTE AS statement. Any ideas how to start the package execution as a different user without using SQL Server Agent and proxy accounts?

I'm trying to configure LinkedServer to use Impersonate. All Servers and PC are connected to the same Domain. What I've configured already: SQLServer1 - First/Middle server: - Configured SPNs for account that is running SQL services - Configured delegation for account that is running SQL services - Configured Delegation for server "Trust this computer for delegation to any service (Kerberos only) SQLServer2 - SecondServer - Configured SPNs for account that is running SQL services - Configured delegation for account that is running SQL services (which I think is not needed but did it anyway) - Configured Delegation for server "Trust this computer for delegation to any service (Kerberos only)(which I think is not needed but did it anyway) Created LinkedServer on SQLServer1 to SQLServer2, in Security tab added AD account that is running SQLServices, added my AD Account and checked 'Impersonate' for both. I've used Kerberos Configuration Manager to verify on both servers and it shows that everything is looking good. **When I'm testing connection while I'm logged on SQLServer1 it passes. When I'm testing it from my PC it returns Error: "The test connection to the linked server failed. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)"** Any help would be greatly appreciated.

I have a database which we want to create database USERS that are NOT mapped to server LOGINS. Then we want to have one server login, but it will EXECUTE AS USER = 'SomeUser' to run queries against that ONE db. But when I try to run my sql block with the EXECUTE AS statement at the top, I am getting the error: > Cannot execute as the database principal because the principal "SomeUser" does not exist, this type of principal cannot be impersonated, or you do not have permission. I've granted the login IMPERSONATE on the user via: GRANT IMPERSONATE ON USER::SomeUser TO [our-server-login] Just for yucks, for testing (this is a test instance), I've even given the login IMPERSONATE ANY LOGIN permission, and that did not help. The database actually already has SET TRUSTWORTHY ON, because it uses a number of .NET Assemblies, and that obviously is not making a difference. Saw [this question](https://dba.stackexchange.com/questions/154878/sql-server-impersonation-is-just-not-working) , which was fixed with TRUSTWORTHY but that is a different scenario (needing to impersonate **logins** across multiple dbs). The db is running in 2019 compatibility mode on a Sql 2019 server.

With MariaDB, is there a way to impersonate a user execution context when connected with a higher privileged account? For instance, when connected with a "MasterUser" execute a SELECT using the more limited privileges of a "ClientUser". The classic idea is to avoid the server application to perform thousands of connections to MariaDB -- if most requests could be handled through a "broker" user which would connect once and operate under the appropriate privilege level for each given transaction. Thank you!

I've configured delegation for SQL server engine and SSRS and it works fine. SQL Server (2017) engine and SSRS installd on the same machine. Let's call it SqlSrv1. What we should attain to: User opens a report with a dataset which joins a database data and an external shared file (with sensitive data) via impersonation. If user has no access to a shared file, report should display him an empty sensitive columns. 1. When I execute BULK INSERT via SSMS, something like

BULK INSERT #t1 FROM '\\FS1\common\anyfile.txt'

it works fine. Computer Management shows my name (instead of sqlengine service account) in "Shared folders\Sessions" as User and SqlSrv1 as Computer. 2. Now when I execute BULK INSERT from SSRS report dataset against a local file (stored on SqlSrv1 C:\anyfile.txt) it works and ProcMon displays my name in Detail column, instead of ssrs service account. 3. At last I execute BULK INSERT from SSRS report dataset against a shared file \\FS1\common\anyfile.txt and I get error Operating system error 5(Access is denied.), but ProcMon displays my name in Detail column as Impersonating. There is a something strange I've located: When I change Data Source in connection string of dataset to FQDN, BULK INSERT begones to work from SSRS. After that I change back Data Source to NetBIOS but BULK INSERT continues to work. What is going on? All fine and dandy, but after restart SSRS service I get error Operating system error 5(Access is denied.) again. When I execute the query from the step 1, ProcMon shows me the path \\FS1\common\\anyfile.txt (with double slash after shared folder name! What?!!) instead of \\FS1\common\anyfile.txt When I get error 5, ProcMon shows \\FS1\common\anyfile.txt (without double slash) But when the step 3 begones to work (after FQDN manipulations), OMG! The ProcMon shows me \\FS1\common\\anyfile.txt (with double slash) and after SSRS service restart I see in ProcMon \\FS1\common\anyfile.txt (without double slash) I've tryed to add double slash to BULK INSERT, but with no luck, SQL engine drops it (as expected). Have you any ideas?

I make an extensive use of SQL Server Service Broker to trigger/parallelize data science tasks (data imports with SSIS or direct queries to another database on the same server, then data management and machine learning with R and/or Python external scripts). I wanted to upgrade from SQL Server 2016 to SQL Server 2017, exact same architecture and scripts, but now I get errors I don't understand, apparently caused by the service broker impersonations and privileges. > The activated proc '[StoredProcTriggerdByServiceBroker]' running on queue 'queuename' output the following: 'The server principal "sbuser" is not able to access the database "mainDB" under the current security context.' To explain a bit a simplification of my process: There are two DBs, mainDB and testDB. ServiceBroker and stored procedures are running on testDB. We mainly use Windows logins to use SQL Server and that was less than ideal for some SSIS purposes, so I chose to create a user called sbuser for which connection is disabled but which we can impersonate, with sufficient privileges on both DBs to do everything required. The queues are triggering the stored procedure as sbuser. The stored procedure fails on statement SELECT * INTO testDB..targetTable from mainDB..sourceTable Same select query, impersonating sbuser manually **works fine**. Same select query, in a stored procedure, with a execute as sbuser / exec StoredProcedure **works fine**. But it fails whenever the stored procedures are triggered by Service Broker. I added to the stored procedure a logging of CURRENT_USER and results of SELECT * into tmp_session FROM sys.dm_exec_sessions WHERE session_id = @@SPID; CURRENT_USER give me sbuser, which is what I expect, but the content of dm_exec_sessions puzzles me as it's different from what I get when I log in as SA, then execute the procedure impersonating sbuser with an execute as. |security_id|login_name|context_info|original_security_id|original_login_name| |--|--|--|--|--| |0x01|sa|0x|0x01|sa| Nothing about sbuser... then again the error message in the log is about sbuser's security context... So, what has changed between SQL Server 2016 and 2017 in the way the Service Broker works and impersonation of logins and their privileges, and how can I run my select into query now?

For clarity, I'm asking how to resolve this 1) *without* running SQL Server as a domain account and 2) *without* making sa the database owner. In a nutshell --- If I install SQL Server 2019 and go with the recommended default user account of NT Service\MSSQLSERVER, and then I connect and run EXECUTE AS LOGIN = [SOMEDOMAIN\someusername];, I get this: > Could not obtain information about Windows NT group/user 'SOMEDOMAIN\someusername', error code 0x5. The problem goes away if I set the SQL Server service to run using a domain account, but I don't want to do this. I like using the recommended NT Service\MSSQLSERVER virtual account. I'm looking for a way to set this up that stays within the dev machine and doesn't require collaborating by setting up a domain account or by manually configuring AD to recognize each development instance of SQL Server. https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15#VA_Desc : > Use a MSA [managed service account] or virtual account when possible. I'd prefer not to use an MSA because it requires setup outside the dev machine. So, is using a virtual account possible when I need to be able to do EXECUTE AS LOGIN = [SOMEDOMAIN\someusername];? What's the configuration I'm missing in order to achieve this? If I'm asking for something impossible, could you explain the conflict? More details --- EXECUTE AS LOGIN = serves as a useful test in SQL Server Management Studio, but here's what's really going on: I'm running software that uses Windows authentication to log into SQL Server and create a new database, including an essential stored procedure that has WITH EXECUTE AS SELF. SELF is the domain account used by Windows authentication when the software connected to SQL Server and created the database. Attempting to execute the stored procedure fails with the same error message that EXECUTE AS LOGIN = does. (If it's useful, I can explain why the software relies on a stored procedure having WITH EXECUTE AS SELF.) I've seen similar questions asked before and get the answer, "just make the database owner sa instead of a domain account." I don't want to do this. It's a big hassle to have to maintain a password for sa and use it instead of Windows authentication every time I deal with this database.

I need to grant a user (MyDomain\JohnSmith) permission to view one particular XE session results using sys.fn_xe_file_target_read_file I thought the best way to do this would be to encapsulate the logic within a stored procedure in a database which John Smith uses called MyDb and then grant him access to the stored procedure. I have solved this so far but it means using impersonation of the dbo user within the stored procedure and setting IS_TRUSTWORTHY on which some Googling suggests is a less secure method and that signing a stored procedure is the most secure way to achieve this. Going the the signing method, I have hit some problems as below: First of all, I create a certificate in the database: USE MyDb GO CREATE CERTIFICATE [CodeSigningCertificate] ENCRYPTION BY PASSWORD = 'SuperSecretPassword' WITH EXPIRY_DATE = '2099-01-01', SUBJECT = 'Code Signing Cert' then I create my stored procedure which has the logic CREATE PROCEDURE MySchema.MyProc AS BEGIN DECLARE @TraceFilePath NVARCHAR(256) = 'Path/To/My/File*.xel' SELECT CONVERT(XML,event_data).value('(/event/@timestamp)', 'NVARCHAR(MAX)' ) AS [TimeStamp], CONVERT(XML,event_data).value('(/event/action[@name="database_name"]/value)', 'NVARCHAR(MAX)' ) AS [Database_name], CONVERT(XML,event_data).value('(/event/data[@name="statement"]/value)', 'NVARCHAR(MAX)' ) AS [Statement], CONVERT(XML,event_data).value('(/event/action[@name="sql_text"]/value)', 'NVARCHAR(MAX)' ) AS SQL_Text, CONVERT(XML,event_data).value('(/event/action[@name="username"]/value)', 'NVARCHAR(MAX)' ) AS Username, CONVERT(XML,event_data).value('(/event/action[@name="client_hostname"]/value)', 'NVARCHAR(MAX)' ) AS Client_Hostname, CONVERT(XML,event_data).value('(/event/action[@name="client_app_name"]/value)', 'NVARCHAR(MAX)' ) AS Client_app_name INTO #Results FROM sys.fn_xe_file_target_read_file (@TraceFilePath, NULL, NULL, NULL ) SELECT * FROM #Results WHERE Statement LIKE '%some value%' ORDER BY SQL_Text END Next, I sign the stored procedure ADD SIGNATURE TO MySchema.MyProc BY CERTIFICATE [CodeSigningCertificate] WITH PASSWORD = 'SuperSecretPassword'; Then I create a user which uses the certificate CREATE USER [CodeSigningUser] FROM CERTIFICATE [CodeSigningCertificate]; At this point, I need to grant the underlying privilege that the is required to run sys.fn_xe_file_target_read_file which is GRANT VIEW SERVER STATE GRANT VIEW SERVER STATE TO [CodeSigningUser] but when I try to do this, I get "Msg 4621, Level 16, State 10, Line 44 Permissions at the server scope can only be granted when the current database is master" which makes perfect sense as it is a server level permission that I am trying to grant to a database level principal I have tried creating a login for the certificate (as I can grant VIEW SERVER STATE to a login) : CREATE LOGIN [CodeSigningLogin] FROM CERTIFICATE [CodeSigningCertificate]; but I get the error Msg 15151, Level 16, State 1, Line 40 Cannot find the certificate 'CodeSigningCertificate', because it does not exist or you do not have permission. Which, again makes sense as the certificate is in a user database I can create the certificate in the master database which allows me to create the login from the certificate but then I can't sign the stored procedure using that certificate as the certificate resides in a different database to the stored procedure. The only way I can think of to accomplish this is to create the Stored Procedure in the master database and then create the certificate, also in the master database, create the login and assign VIEW SERVER STATE to the login. Is there a way I can keep my stored procedure in MyDb and have a user MyDomain\JohnSmith execute it to be able to see the XE Session Results?

Assume that there is a certificate created in database create certificate certName with subject = 'subj'; GO And a user mapped to this certificate create user userName from certificate certName; GO Trying to impersonate this user directly execute as user = 'userName'; GO or specifying user in the execute as clause of the module create procedure procName with execute as 'userName' as set nocount on; GO returns error > Msg 15517, Level 16, State 1 ...
Cannot execute as the database > principal because the principal "userName" does not exist, this type > of principal cannot be impersonated, or you do not have permission. However, I was not able to find this restriction mentioned in documentation (here and here ), where the only relevant statement seems is > user_name must exist in the current database and must be a singleton > account. user_name cannot be a group, role, certificate, key, or > built-in account, such as NT AUTHORITY\LocalService, NT > AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem. Is it possible to impersonate user (or login) mapped to certificate or not?

I am trying to allow two users to run a single agent job. With this in mind, I don't want to add the users to the SQLAgentOperatorRole role in msdb as that will give them excess priveleges. I tried creating a stored procedure which will run as the owner (dbo): Create the stored procedure (logged in with a sysadmin account) CREATE LOGIN [UnPriveleged] WITH PASSWORD = 'MyPassword' GO USE AdventureWorks2017 GO CREATE USER UnPriveleged FOR LOGIN [UnPriveleged] GO CREATE PROCEDURE spRunJOb WITH EXECUTE AS OWNER AS SELECT CURRENT_USER SELECT SYSTEM_USER EXEC msdb..sp_start_job @job_name = 'test' GO GRANT EXECUTE ON spRunJOb TO UnPriveleged and then I run it as the Unpriveleged user in another session: SELECT CURRENT_USER EXECUTE [spRunJOb] and I get an error Msg 229, Level 14, State 5, Procedure msdb..sp_start_job, Line 1 [Batch Start Line 0] The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'. I thought this would run as the login for the owner of the stored procedure (sa) as that is a sysadmin and should be able to do everything. The output I get from the selects confirms the procedure is executing as the sa system user. The overall SELECT output is Unpriveleged dbo sa How can I achieve what I want here?

During some reading, got to know about the Impersonate permissions. From what I've read, it is more like creating a copy of the user with all the permission levels under a different name. I understand that this can be used for executing any queries under a different login but ultimately what purpose does it serve ? Why was this feature introduced?

I have a Stored Procedure that creates a Database for a specific project which needs to run every month by one of our Data Analysts. The question is how do I structure this to enable the Analyst to run this Stored Procedure without granting them Create Database Permission. I Tried WITH EXECUTE AS OWNER/USER_NAME The Analysts still get the below error: Msg 262, Level 14, State 1, Line 67 CREATE DATABASE permission denied in database 'master'. Any advice?

This question is about impersonation in Microsoft SQL Server. I want to know that if I have impersonation permissions on a specific login then I can easily impersonate that login whenever I want and I do not need to have a stored procedure with EXECUTE AS OWNER? If that is so then what is the purpose of a stored procedure with EXECUTE AS?

I created a stored procedure to allow users to Close All Db connections in the QA environment. I am SA and created the procedure. When I modify the SP , run it Without 'Execute as Owner', I get results. When I add 'Execute as Owner', I do not receive any results. Trying to understand why. create procedure [dbo].[DatabaseConnectionClose] @DatabaseName varchar(255) with execute as owner as DECLARE @kill varchar(8000) = ''; SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';' FROM sys.dm_exec_sessions WHERE database_id = db_id(@DatabaseName) select @kill as CloseConnectionScript EXEC(@kill);

One of my DBAs is trying to look at the database properties of a database by right clicking and selecting properties and is getting the following error: > Cannot show requested dialog. (SqlMgmt) > > An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) > > Cannot execute as the database principal because the principal "guest" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Microsoft SQL Server, Error: 15517) Points of interest: - The guest account has been disabled (no connect permission) - The DBA is a member of db_owner on the database, but not sysadmin on the instance. - He has this permission through a role, but does not have a user directly in the instance let alone the database. - This is a windows authenticated login. - I tried creating a SQL Id and testing but couldn't replicate. - The version of the instance is SQL Server 2016 (SP1) - 13.0.4001.0 (X64) - On Windows Server 2012 R2 Standard 6.3 What I don't understand is why he would be impersonating anything since he is a member of db_owner, particularly when trying to open the properties dialog. I've checked here: https://dba.stackexchange.com/questions/134026/cannot-execute-as-the-database-principal-because-the-principal-guest-does-not but the service account running the named account has sysadmin access so this shouldn't be a problem.

I have a situation that, while I was able to work around it (as the repro will show), I don't understand. Here are the high points - Two databases, ChainingSource and ChainDestination, both of which have cross database chaining set to true - A stored procedure in ChainingSource accesses, through an EXEC(@sql), accesses a table in ChainingDestination - The stored procedure is defined with an execute as clause - If I try to execute the procedure as is, it says the server principal of the execution context is unable to access ChainingDestination - So I add a certificate and code signing into the mix. That is, I add a certificate mapped login to the server, mapped user to each of the databases, and grant permissions to the certificate mapped user accordingly - If I leave the execute as clause in place, I get the same error. - If I remove the execute as clause, everything is fine. It's the second-to-last point that I'm confused about. Or, specifically, why that one doesn't work and the last one *does*. ---------- /****************************** Setup ******************************/ USE [master]; go IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = 'ChainingSource') BEGIN ALTER DATABASE [ChainingSource] SET OFFLINE WITH ROLLBACK IMMEDIATE; ALTER DATABASE [ChainingSource] SET ONLINE; DROP DATABASE [ChainingSource]; END IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = 'ChainingDestination') BEGIN ALTER DATABASE [ChainingDestination] SET OFFLINE WITH ROLLBACK IMMEDIATE; ALTER DATABASE [ChainingDestination] SET ONLINE; DROP DATABASE [ChainingDestination]; END GO EXECUTE AS LOGIN = 'sa'; CREATE DATABASE [ChainingSource]; CREATE DATABASE [ChainingDestination]; GO REVERT; GO ALTER DATABASE [ChainingSource] SET DB_CHAINING ON; ALTER DATABASE [ChainingDestination] SET DB_CHAINING ON; IF SUSER_ID('myAppUser') IS null CREATE LOGIN [myAppUser] WITH password = 'p@ssw0rd!23'; IF SUSER_ID('myAppUserEscalated') IS null CREATE LOGIN [myAppUserEscalated] WITH password = 'p@ssw0rd!23'; IF NOT EXISTS ( SELECT * FROM sys.[symmetric_keys] AS [sk] WHERE name = '##MS_DatabaseMasterKey##' ) BEGIN CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'f00bar!23'; PRINT 'Created master key in databse [master]'; END IF CERT_ID('myAppCert') IS NULL CREATE CERTIFICATE [myAppCert] AUTHORIZATION dbo FROM BINARY = 0x308201BD30820126A003020102021061AF3EB269776BB74629F44629EF9216300D06092A864886F70D0101050500301C311A301806035504031311436F6465205369676E696E6720436572743020170D3136313032303232303932335A180F32303939303130313030303030305A301C311A301806035504031311436F6465205369676E696E67204365727430819F300D06092A864886F70D010101050003818D0030818902818100BCCA7DC1CF2F4874F341AF3C586F0B023CAAD16986ADDAC2F7BFB3BCE590F2A952218F51298067CE3BE9ED695A229DADD029510F0927F30484A587024E0F58EC83924BE49D227D2FE1FCCA0C682528D6A0658AAA6CA5D9F2405AB6950B7D5BA672BB971910D71CEE3B77FF0A4EF59F010AAB445FD127944966C141F7CFC3D5790203010001300D06092A864886F70D010105050003818100011525EDD191767041659701F13F4370F803E6C981A6E33D68863FDACADE709926AB7E3BC8D618EDD07FC52058EFE42D96CA49961CF2936F446EDC4B7D55725FF2F1B37B326D564941CF6A7424551828FE198335AEFA0C892B375D3B676F35B708A48C67F80714643A34050CF9C557FBDD01274BC1ACCCA9A7AD3EC37DD2DA31 WITH PRIVATE KEY (BINARY = 0x1EF1B5B00000000001000000010000001000000054020000CF21B85A9464B60CDAB9F2E419B341490702000000A4000002E67BDF3CE02406E4D69A760D519B3BB6DA77FAFA7D710936EAE5267F072F98A1F7521110EB03955427B79FA386F7D70EDF6E977E92E59761DE0EB186F895AD975CE63C4D8A8B67BA487B9807EDB8B33C7C08EABCCD716E9505170A9729B6E165CE0ED0CA35B5C62D548367FEFC2C694060184D9185331466A0C64A9CB7BF8CA7AC0946A54091A4626978320C7290A784C6147BAF23FD866D9D1D4D1D79DA1B4D2DE213D11299F1417D8C421CC25A2E851FF9CEFA0ECA2006186C787692FDC28F2E702FCC7E76AFBEB95B954B50AA3697E60FC6928392664CE0EDB794AA392C1CC6326102B7CE8A02B367D2F416269DBBF4C16F096780517D32B4653E94DE5C24CE9D39EBC8E6A4EF1B9217F1B4F098F4F77F88CC11C40DDB312BE87CC1430C16AED8773E7691ADE8472BFC02B458B09B40404F61D2E02746AE576582DEE3EC5C09077E127BB4E996A9C4A840E6E0F59D85A3FC4E2844679927DBA6A571927A1F1C938716B8FC922B1D77FAA90BDBA49D1084081E4198A50506C5F6FE87F81B759EE0688428ABA7B2E8CC7D96AC6409DAE41937DB9C1E1CACCD7AE86A8F161316A07B05D523A116AB87022978312EE9853AE9FFA44FFF52114D084934D86D0FFD2D47B974769812BF0F4FE8276FD0DCE4069F11EC3915A68F4454166E3ABAAB9539530117597EE52213FEC7C87254634F10062C5C1D97CE5FEABB13252B22E210F56DB281FC1CE5432A7144FB4B89D00B4F8BC876C8C0F397DB9D22E15E2B07FBB44ADDDFBB6A75728917AC330E3A9F978847AC2D27913B3B6CBF54F1BAEF06072D15050ED1CA7BF9C5763A, DECRYPTION BY PASSWORD = 'f00bar!23') IF SUSER_ID('myAppCert') IS NULL CREATE LOGIN [myAppCert] FROM CERTIFICATE [myAppCert]; USE [ChainingDestination]; CREATE USER [myAppUser]; CREATE USER [myAppUserEscalated]; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'f00bar!23'; CREATE CERTIFICATE [myAppCert] AUTHORIZATION dbo FROM BINARY = 0x308201BD30820126A003020102021061AF3EB269776BB74629F44629EF9216300D06092A864886F70D0101050500301C311A301806035504031311436F6465205369676E696E6720436572743020170D3136313032303232303932335A180F32303939303130313030303030305A301C311A301806035504031311436F6465205369676E696E67204365727430819F300D06092A864886F70D010101050003818D0030818902818100BCCA7DC1CF2F4874F341AF3C586F0B023CAAD16986ADDAC2F7BFB3BCE590F2A952218F51298067CE3BE9ED695A229DADD029510F0927F30484A587024E0F58EC83924BE49D227D2FE1FCCA0C682528D6A0658AAA6CA5D9F2405AB6950B7D5BA672BB971910D71CEE3B77FF0A4EF59F010AAB445FD127944966C141F7CFC3D5790203010001300D06092A864886F70D010105050003818100011525EDD191767041659701F13F4370F803E6C981A6E33D68863FDACADE709926AB7E3BC8D618EDD07FC52058EFE42D96CA49961CF2936F446EDC4B7D55725FF2F1B37B326D564941CF6A7424551828FE198335AEFA0C892B375D3B676F35B708A48C67F80714643A34050CF9C557FBDD01274BC1ACCCA9A7AD3EC37DD2DA31 WITH PRIVATE KEY (BINARY = 0x1EF1B5B00000000001000000010000001000000054020000CF21B85A9464B60CDAB9F2E419B341490702000000A4000002E67BDF3CE02406E4D69A760D519B3BB6DA77FAFA7D710936EAE5267F072F98A1F7521110EB03955427B79FA386F7D70EDF6E977E92E59761DE0EB186F895AD975CE63C4D8A8B67BA487B9807EDB8B33C7C08EABCCD716E9505170A9729B6E165CE0ED0CA35B5C62D548367FEFC2C694060184D9185331466A0C64A9CB7BF8CA7AC0946A54091A4626978320C7290A784C6147BAF23FD866D9D1D4D1D79DA1B4D2DE213D11299F1417D8C421CC25A2E851FF9CEFA0ECA2006186C787692FDC28F2E702FCC7E76AFBEB95B954B50AA3697E60FC6928392664CE0EDB794AA392C1CC6326102B7CE8A02B367D2F416269DBBF4C16F096780517D32B4653E94DE5C24CE9D39EBC8E6A4EF1B9217F1B4F098F4F77F88CC11C40DDB312BE87CC1430C16AED8773E7691ADE8472BFC02B458B09B40404F61D2E02746AE576582DEE3EC5C09077E127BB4E996A9C4A840E6E0F59D85A3FC4E2844679927DBA6A571927A1F1C938716B8FC922B1D77FAA90BDBA49D1084081E4198A50506C5F6FE87F81B759EE0688428ABA7B2E8CC7D96AC6409DAE41937DB9C1E1CACCD7AE86A8F161316A07B05D523A116AB87022978312EE9853AE9FFA44FFF52114D084934D86D0FFD2D47B974769812BF0F4FE8276FD0DCE4069F11EC3915A68F4454166E3ABAAB9539530117597EE52213FEC7C87254634F10062C5C1D97CE5FEABB13252B22E210F56DB281FC1CE5432A7144FB4B89D00B4F8BC876C8C0F397DB9D22E15E2B07FBB44ADDDFBB6A75728917AC330E3A9F978847AC2D27913B3B6CBF54F1BAEF06072D15050ED1CA7BF9C5763A, DECRYPTION BY PASSWORD = 'f00bar!23') CREATE USER [myAppCert]; GO CREATE TABLE [dbo].[topSecret] ([ID] INT IDENTITY, [Secrets] NVARCHAR(100)); INSERT INTO [dbo].[topSecret] ([Secrets]) VALUES ('Nuke Codes!'); GRANT SELECT ON [dbo].[topSecret] TO [myAppUserEscalated]; GRANT SELECT ON [dbo].[topSecret] TO [myAppCert]; GO USE [ChainingSource]; GO CREATE USER [myAppUser] CREATE USER [myAppUserEscalated]; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'f00bar!23'; CREATE CERTIFICATE [myAppCert] AUTHORIZATION dbo FROM BINARY = 0x308201BD30820126A003020102021061AF3EB269776BB74629F44629EF9216300D06092A864886F70D0101050500301C311A301806035504031311436F6465205369676E696E6720436572743020170D3136313032303232303932335A180F32303939303130313030303030305A301C311A301806035504031311436F6465205369676E696E67204365727430819F300D06092A864886F70D010101050003818D0030818902818100BCCA7DC1CF2F4874F341AF3C586F0B023CAAD16986ADDAC2F7BFB3BCE590F2A952218F51298067CE3BE9ED695A229DADD029510F0927F30484A587024E0F58EC83924BE49D227D2FE1FCCA0C682528D6A0658AAA6CA5D9F2405AB6950B7D5BA672BB971910D71CEE3B77FF0A4EF59F010AAB445FD127944966C141F7CFC3D5790203010001300D06092A864886F70D010105050003818100011525EDD191767041659701F13F4370F803E6C981A6E33D68863FDACADE709926AB7E3BC8D618EDD07FC52058EFE42D96CA49961CF2936F446EDC4B7D55725FF2F1B37B326D564941CF6A7424551828FE198335AEFA0C892B375D3B676F35B708A48C67F80714643A34050CF9C557FBDD01274BC1ACCCA9A7AD3EC37DD2DA31 WITH PRIVATE KEY (BINARY = 0x1EF1B5B00000000001000000010000001000000054020000CF21B85A9464B60CDAB9F2E419B341490702000000A4000002E67BDF3CE02406E4D69A760D519B3BB6DA77FAFA7D710936EAE5267F072F98A1F7521110EB03955427B79FA386F7D70EDF6E977E92E59761DE0EB186F895AD975CE63C4D8A8B67BA487B9807EDB8B33C7C08EABCCD716E9505170A9729B6E165CE0ED0CA35B5C62D548367FEFC2C694060184D9185331466A0C64A9CB7BF8CA7AC0946A54091A4626978320C7290A784C6147BAF23FD866D9D1D4D1D79DA1B4D2DE213D11299F1417D8C421CC25A2E851FF9CEFA0ECA2006186C787692FDC28F2E702FCC7E76AFBEB95B954B50AA3697E60FC6928392664CE0EDB794AA392C1CC6326102B7CE8A02B367D2F416269DBBF4C16F096780517D32B4653E94DE5C24CE9D39EBC8E6A4EF1B9217F1B4F098F4F77F88CC11C40DDB312BE87CC1430C16AED8773E7691ADE8472BFC02B458B09B40404F61D2E02746AE576582DEE3EC5C09077E127BB4E996A9C4A840E6E0F59D85A3FC4E2844679927DBA6A571927A1F1C938716B8FC922B1D77FAA90BDBA49D1084081E4198A50506C5F6FE87F81B759EE0688428ABA7B2E8CC7D96AC6409DAE41937DB9C1E1CACCD7AE86A8F161316A07B05D523A116AB87022978312EE9853AE9FFA44FFF52114D084934D86D0FFD2D47B974769812BF0F4FE8276FD0DCE4069F11EC3915A68F4454166E3ABAAB9539530117597EE52213FEC7C87254634F10062C5C1D97CE5FEABB13252B22E210F56DB281FC1CE5432A7144FB4B89D00B4F8BC876C8C0F397DB9D22E15E2B07FBB44ADDDFBB6A75728917AC330E3A9F978847AC2D27913B3B6CBF54F1BAEF06072D15050ED1CA7BF9C5763A, DECRYPTION BY PASSWORD = 'f00bar!23') CREATE USER [myAppCert]; GO CREATE SYNONYM [dbo].[topSecret] FOR [ChainingDestination].[dbo].[topSecret]; GRANT SELECT ON [dbo].[topSecret] TO [myAppUserEscalated]; GRANT SELECT ON [dbo].[topSecret] TO [myAppCert]; GO IF OBJECT_ID('[dbo].[getSecrets]') IS NOT null DROP PROCEDURE [dbo].[getSecrets] GO CREATE PROCEDURE [dbo].[getSecrets] WITH EXECUTE AS 'myAppUserEscalated' AS BEGIN SELECT * FROM sys.login_token; SELECT * FROM sys.user_token; EXEC('SELECT * FROM [dbo].[topSecret] AS [ts];'); END GO GRANT EXECUTE ON [dbo].[getSecrets] TO [myAppUser]; GO /****************************** DEMO ******************************/ -- EXECUTE AS clause only EXECUTE AS LOGIN = 'myAppUser'; GO EXEC dbo.[getSecrets] GO REVERT; GO -- no bueno. let's try to add a signature! ADD SIGNATURE TO [dbo].[getSecrets] BY CERTIFICATE [myAppCert]; EXECUTE AS LOGIN = 'myAppUser'; GO EXEC dbo.[getSecrets] GO REVERT; GO -- still no bueno. -- let's take off the EXECUTE AS clause and sign ALTER PROCEDURE [dbo].[getSecrets] AS BEGIN SELECT * FROM sys.login_token; SELECT * FROM sys.user_token; EXEC('SELECT * FROM [dbo].[topSecret] AS [ts];'); END GO ADD SIGNATURE TO [dbo].[getSecrets] BY CERTIFICATE [myAppCert]; EXECUTE AS LOGIN = 'myAppUser'; GO EXEC dbo.[getSecrets] GO REVERT; GO -- bueno

On MSSQL 2008R2 I have two instances on different servers, and I need sync up the password changes for all logins. I thing that i could create a store procedure where a user (impersonate) with securityadmin server role change the password. Obs: CHANGE_PASSWORD is a user with securityadmin server role. ALTER PROCEDURE SPU_CAMBIO_PASSWORD ( @USER VARCHAR(50), @PASSWORD VARCHAR(100) ) WITH EXECUTE AS 'CHANGE_PASSWORD' AS DECLARE @STATEMENT VARCHAR(200) SET @STATEMENT= 'ALTER LOGIN '+ @USER+ ' WITH PASSWORD = '''+@PASSWORD+''', CHECK_POLICY =OFF' EXEC(@STATEMENT) GO Error: Cannot execute as the server principal because the principal "CAMBIO_PASSWORD" does not exist, this type of principal cannot be impersonated, or you do not have permission.

I would like to enable an application that dumps a big ol' heap of data into a Staging area to be able to kickoff the stored procedure that transforms & loads the data into the Production area. If possible, I'd like to **not** grant the application any access to the production area. Unfortunately, [EXECUTE AS](https://learn.microsoft.com/en-us/sql/t-sql/statements/execute-as-clause-transact-sql) seems to be failing me. For reference, the DBs in question are running on 2008 R2, but I'm recreating the behavior on my 2016 localhost. As well, the service account in question is a domain credential, but I've recreated the behavior with a SQL Auth credential for portability. ### The Setup use [master] create database Prod; create database Stage; create login ServiceAccount with password='Password1234'; go use Prod create table tbl1 ( i int ); insert tbl1 select 1; go use Stage create user ServiceAccount for login ServiceAccount; go create proc spLoadStageToProd as begin insert Prod.dbo.tbl1(i) select checksum(newid())%100; -- random number end; go grant execute on spLoadStageToProd to ServiceAccount; go ### The Testing From the Stage database, exec spLoadStageToProd; succeeds. Predictably, exec as login = 'ServiceAccount'; exec spLoadStageToProd; fails with the error... Msg 229, Level 14, State 5, Procedure spLoadStageToProd, Line 1 [Batch Start Line 21] The EXECUTE permission was denied on the object 'spLoadStageToProd', database 'Stage', schema 'dbo'. Okay, so I'll try execute as owner so that the proc has the permissions it needs to do the work but the (*developer who has the password for*) ServiceAccount doesn't get to muck about in my Production area. alter proc spLoadStageToProd with execute as owner as begin insert Prod.dbo.tbl1(i) select checksum(newid())%100; -- random number end; go Shoot! Well now a simple test from Staging of exec spLoadStageToProd; returns the error: Msg 916, Level 14, State 1, Procedure spLoadStageToProd, Line 5 [Batch Start Line 35] The server principal "DOMAIN\peter" is not able to access the database "Prod" under the current security context. "DOMAIN\peter" is of course me: the doof with sysadmin server role... Well that's weird enough... I tried alter authorization on object::spLoadStageToProd to dbo; but the same error (Msg 916) pops up. What's more... exec as login = 'ServiceAccount'; exec spLoadStageToProd; now returns... Msg 229, Level 14, State 5, Procedure spLoadStageToProd, Line 1 [Batch Start Line 37] The EXECUTE permission was denied on the object 'spLoadStageToProd', database 'Stage', schema 'dbo'. Screw it! alter authorization on object::spLoadStageToProd to sa;... Msg 15151, Level 16, State 1, Line 41 Cannot find the user 'sa', because it does not exist or you do not have permission. ... ... ...[sad](https://media.giphy.com/media/Jq7y34Hgfy01y/giphy.gif) . So... clearly I'm misunderstanding something fundamental. I tried enabling [cross-db ownership chaining](https://msdn.microsoft.com/en-us/library/bb669059(v=vs.110).aspx) just in case that was the sticky wicket but no luck. After fruitless googling and a quick scan of [these](https://dba.stackexchange.com/questions/19324/cross-database-chaining-vs-trustworthy-option) [two](https://dba.stackexchange.com/questions/152931/stored-procedure-security-with-execute-as-cross-database-queries-and-module-si) dba.se questions, I'm pretty stuck. Where am I going wrong? # How do I get this SProc to run on elevated cross-database permissions regardless of who calls it? The project that precipitated this question is an iterative deprecation so I think cert-signing may end up being a rather large administrative overhead as time goes on. If it is possible to use this stored proc as a wrapper to execute restricted actions agnostic of _who_ calls it, that's the end-goal. I am confident that the SProc cannot be improperly modified, so anything that executes from inside it may be highly permissioned - in the hopes that I don't need to administer perms for the ServiceAccount one-by-one. ---- ### Courtesy Cleanup Script revert; use [master] go drop database Prod,Stage; drop login ServiceAccount; go