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

2

April 11, 2019 by Kenneth Fisher

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.

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

  1. notarian says:

    I wonder if you can use SQL Users to authenticate in a contained database on a Windows Auth only instance?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,492 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: