2

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.

4
  • Thanks for the additional information. So the actual question is How do I down-grade a SQL Server 2017 database to SQL Serer 2016?. That can be done pretty easy depending on what kind of objects are used in the 2017 database.
    – John K. N.
    Feb 4 '21 at 14:57
  • No, I have no problem downgrading. I just want to also transfer database users of Windows Users type, which I cannot...that's what I am asking for.
    – jericzech
    Feb 4 '21 at 14:59
  • I downgraded some SQL Server 2019 database to 2017 last year. Will try and find the scripts. I'm not sure if the Domain\Users were included though.
    – John K. N.
    Feb 4 '21 at 15:14
  • I found the scripts i had generated via SSMS and the Generate Scripts feature in the UI when selecting a database. However, the domain\user SQL Server Logins which were generated in the script were not created on the new server, due to the fact that I had set the script to continue on error and because the user couldn't be created because of the issues already mentioned by others in the answers. So you are stuck with the solutions mentioned in the answers here. Good luck.
    – John K. N.
    Feb 12 '21 at 9:58
4

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.

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.

2

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}];
    
2
  • 1
    Thank you Solomon, it's all pretty much obvious now. The easiest and the most straightforward approach for me would be script out users from original database (on newer SQL version) and then run the script in the customers environment after I restore downgraded database. It's not gonna be all-in-one package (backup) but it's acceptable.
    – jericzech
    Feb 5 '21 at 7:52
  • @jericzech Yer welcome. I added some notes if you want to review the options again. If it were me, I would go with Option #2 as it provides the most turn-key solution: in the end, you just restore. This way you can have schema / object permissions, DB role memberships, etc set up ahead of time with no need for any further DB modifications upon delivery, except for possibly updating the DB owner. Feb 5 '21 at 13:53
1

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.

5
  • But I want to create orphaned user, without login...why should be SID needed under this circumstances?
    – jericzech
    Feb 4 '21 at 13:23
  • @jericzeh I understand, and I know normally you can create an orphaned User without a Login but there's almost 0 reason to do so, so perhaps Microsoft shouldn't've allowed that feature. But that's neither here nor there, 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.
    – J.D.
    Feb 4 '21 at 14:02
  • 1
    [@]J.D. please see my scenario which I consider valid in my addition to the post
    – jericzech
    Feb 4 '21 at 14:42
  • @jericzech Thanks for the clarification, unfortunately I don't think it changes my answer (as the other answers seem to agree), because in your Production environment your Windows Users will have an associated Login to them (either to the domain user itself or an AD group that domain user belongs to). So even if you could recreate the orphaned Users, they'd remain orphaned and you'd still have to create their corresponding Logins after the downgrade in order to mimic what you have in Production. The correct way to do this would be to create the downgraded version in a new server...
    – J.D.
    Feb 4 '21 at 15:41
  • ...on the domain and then switch servers after the downgrade is complete. Furthermore it's even generally best practice to let both servers run in parallel for a short time until the new SQL Server instance has been verified to be working as expected before cutting over.
    – J.D.
    Feb 4 '21 at 15:42
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.