CREATE Windows User in database for not existing domain

Question

Is it possible to create a Windows User in the database when the domain is not accessible?

I am preparing database at my dev environment outside of the targeted domain. Created user is not expected to work at my dev of course.

I am trying:

CREATE USER [NOTEXISTINGDOMAIN\User1] WITHOUT LOGIN;

But getting an error:

Windows NT user or group 'NOTEXISTINGDOMAIN\User1' not found. Check the name again.

I have searched for help and it seems there is no way...which I don't believe much cause this seems to me like relevant scenario.

ADDITION: Let me explain my scenario. My task is to downgrade database to older MS SQL Server (SQL 2017 to SQL 2016 to be precise). I cannot do this in customers environment (and domain, DC) cause obviosly there is no both MS SQL versions available. So I amd doing the downgrade in my dev. And I want to prepare the database for my customer with all objects in it, including users of all types, including Windows Users - so when my customer restores the database everything is ready and the same as in original database.

Obviosly I'll have to create users later after restoring the database in the customers environment.

Answer 1

No, it isn't possible. SQL needs to contact the DC to collect the user/group SID and needs to save it inside the sys.server_principals table in master database.

Answer 2

The user is identified by the SID, not the name. See sys.database_principals. If the SID isn't possible to get to (either because it doesn't exist as a login or as in your case a domain controller isn't available), then it isn't possible to create the user.

MS could allow us to specify a SID in the CREATE USER command, just as we can do in the CREATE LOGIN command (which is there for a different scenario), but they don't allow for that. You can of course request for such a feature at https://feedback.azure.com/forums/908035-sql-server, but for that to be introduced in a future version would probably require more than just a few votes.

Answer 3

When creating Logins or Users based on a Windows account, that Windows Login or Windows Group needs to be accessible as it's the source of the SID.

Now, it is possible to create a Login by supplying an SID, but that only works for SIDs that are for SQL Server accounts.

Also, Users created using WITHOUT LOGIN aren't simply orphaned Users that can be connected to a Login later. True "Users without a Login" are permanently "without a Login". When creating a User based on a Windows account and also using WITHOUT LOGIN, the WITHOUT LOGIN part is pretty much ignored as it isn't relevant given that SQL Server isn't handling the authentication (Windows / Active Directory is).

If you do create a real User without a Login, you won't be able to connect it to a Login later:

Msg 33016, Level 16, State 1, Line XXXXX
The user cannot be remapped to a login. Remapping can only be done for users that were mapped to Windows or SQL logins.

Creating a SQL Server Login with the intention of mapping it to your client's Windows account later, that also won't work:

Msg 33017, Level 16, State 1, Line XXXXX
Cannot remap a user of one type to a login of a different type. For example, a SQL user must be mapped to a SQL login; it cannot be remapped to a Windows login.

One thing you can do, though, is remap a User's name and SID at the same time with ALTER USER. I just tried this with a Windows account associated with only a User (no Login). I had deleted the Windows account and then remapped the User, and the principal_id (in sys.database_principals), and even the create_date of that principal, stayed the same.

This leaves two possibilities as far as I can see:

1. Remap Users created locally:

   1. Create dummy Windows accounts in your Domain (but with the client's account names to help keep everything straight), or even local Windows accounts on the computer itself.

      CREATE USER [YourDomain\ClientLoginName];
      

   2. When finished, Detach / Backup the database and then Attach / Restore it onto the client system.
   3. If the client's instance doesn't already have the Logins created, you will then need to create the Logins.
   4. After creating the Logins, remap the Users using:

      ALTER USER [YourDomain\ClientLoginName]
        WITH LOGIN = [ClientDomain\ClientLoginName];
      

2. Get the correct client Logins / SIDs ahead of time:

   1. Install SQL Server Express 2016 or even SQL Server Express LocalDB 2016 at the client (assuming they don't have it, else use their existing SQL Server 2016 if they do have it)
   2. Create a new database in SQL Server 2016
   3. Add the desired Users from the client's Domain
   4. Detach / Backup the database and then Attach / Restore it onto your system.
   5. When finished, Detach / Backup the database and then Attach / Restore it onto the client system.
   6. If the client's instance doesn't already have the Logins created, you will then need to create the Logins (but not the Users as they already exist in the database).

   This option doesn't require any remapping. As long as you don't need to test the database on your system because these Users won't be able to log in. But, you should still be able to grant them permissions and add them to database roles (it worked in my limited test).

3. IN BOTH CASES: The database will be owned by the Login that did the Attach / Restore, so you will most likely need to:

   ALTER AUTHORIZATION ON DATABASE::[{RestoredDatabase}]
     TO [{LoginWhoShouldOwnTheDatabase}];
   

Answer 4

I don't believe it's possible, as MBuschi mentioned the SID of the Domain User is required to create the Login on the server such that a User can be created in the database you're trying to add them to. (A Database User needs a correlating SQL Server Login inside the SQL instance.) So you'd have to have access to the domain from your Dev environment at least initially to create the Login. Then likely after the Login has been created (and therefore the SID has been mapped) you can remove access to the domain from your Dev environment and should be able to create the appropriate Database Users as needed, from that Login.

To create just an orphaned User you can just do: CREATE USER [User1] WITHOUT LOGIN; because the context of a domain doesn't exist at the User level, only the Login level.

Answer 5

But I want to create orphaned user, without login...why should be SID needed under this circumstances?

You should read this BOL article: CREATE USER (Transact-SQL)

Users based on Windows principals that connect through Windows group logins

User based on a Windows user that has no login, but can connect to the Database Engine through membership in a Windows group. CREATE USER [Contoso\Fritz];

User based on a Windows group that has no login, but can connect to the Database Engine through membership in a different Windows group. CREATE USER [Contoso\Fritz];

So in the syntax you use, the domain user is recognized.

And it's a common situation when your domain user has no corresponding login but access server through membership in some Windows group. In this case there is no and just can not be the corresponding sid in sys.server_principals, the user is validated on domain controller that is unavailable in your case.