Can SQL Ids be used on an instance with Windows Authentication only?

tl;dr; Yes, but.

It’s generally considered more secure to have your SQL Server instance set to Windows Authentication and not allow SQL Server principals, but does that mean we can’t use SQL principals at all?

What you can’t do is log in. However, creating SQL principals and using them in impersonation is not a problem.

CREATE LOGIN MySQLId WITH PASSWORD = 'MySQLId', CHECK_POLICY = OFF;
USE Test;
GO
CREATE USER MySQLId FROM LOGIN MySQLId;

You can even create a user without a login.

CREATE USER MySQLId2 WITHOUT LOGIN;

And why would you want to do something silly like creating a principal on a server that won’t let it log in?

Impersonation! (For those of you that aren’t aware impersonation lets you use permissions granted to a different principal.)

Here is an example of granting permissions to a SQL id and then using impersonation in a stored procedure to actually use that permission. (And I’m even going to use another SQL id to impersonate to demonstrate the permissions usage.

-- Grant read permissions to MySQLId.
ALTER ROLE db_datareader ADD MEMBER MySQLId; 
GO
-- Create a table to read from.
CREATE TABLE MyTable (Col1 int);
GO
INSERT INTO MyTable VALUES (1), (2), (3);
GO
-- Create proc that uses MySQLId's read permissions.
CREATE PROCEDURE ReadMyTable 
WITH EXECUTE AS 'MySQLId'
AS
SELECT Col1 FROM MyTable;
GO

-- Grant execute permissions on the proc to MySQLId2.
GRANT EXECUTE ON ReadMyTable TO MySQLId2;
GO
-- Execute as MySQLId2. It has no permissions other than 
-- connect and execute on the proc. It doesn't even have  
-- a server level principal.
EXECUTE AS USER = 'MySQLId2';
GO
-- Since I'm running as MySQLId2 I don't have read permissions.
SELECT Col1 FROM MyTable;

Msg 229, Level 14, State 5, Line 31

The SELECT permission was denied on the object ‘MyTable’, database ‘Test’, schema ‘dbo’.

I’m a sysadmin on my instance so I see this as proof that my permissions have changed to that of MySQLId2. Even though MySQLId2 is a SQL id on a box that is Windows Authentiction only. So now let’s try running the stored procedure we created.

EXEC ReadMyTable;
GO

tl; but you read it anyway

On a server that is set to Windows Authentication only you can’t log in using a SQL Server authenticated id, but they can be really handy to use with impersonation.