Sql-server – Create domain user offline

sql serversql-server-2016users

Can I add domain users to a database, when Sql Server A (the development server) is not part of the domain and has absolutely no access to the domain?

The domain users are of course not expected to work on server A, but these users are already configured on Sql Server B, the production sql server.

When a backup is taken on the development server A and deployed on the production server B, currently these users have to be re-added to the database on server B.

(The users do not have sysadmin rights on server B. As far as I know, if they had sysadmin rights, they would automatically have rights to access any new databases anyway)

Is it somehow possible to CREATE USER domain\xy on development server A, so that they will work when restoring a backup on the production server B?

Or as a workaround, could I take a blank database backup with only the users from server B, and then use that backup as a base to create new databases on server A?

Best Answer

Short answer: your workaround is fine, other than that: no.

Long answer:

It is not possible to create a user for a non existing domain, though the error message is somewhat strange:

CREATE USER [INVALIDNAME\SOMEUSER] FOR LOGIN [VALIDNAME\SOMEUSER] WITH DEFAULT_SCHEMA=[dbo]
GO
Msg 15006, Level 16, State 1, Line 5
'INVALIDNAME\SOMEUSER' is not a valid name because it contains invalid characters.

  • Running the same with "VALIDNAME" domain on both user and login side works fine.

  • even if it would work, it would create a bunch of other issues for you (SID of INVALIDNAME\SOMEUSER not valid, mapped to wrong login, etc)

Your Workaround

With your workaround, on the other hand, it will be okay as the users are not touched when you restore the database on any server.

Another Workaround

Create a staging instance in the same domain as server B is, create all logins you need and create a database that contains all the users mapped to the logins you need.

Then, restore both, the master and your database, in a new instance running outside your domain.

I haven't tested this, but I am quite sure it should work.

Related Question