For clarity, I'm asking how to resolve this
- without running SQL Server as a domain account and
- without making
sathe 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.
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?
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.