Is user mapping required for database login in MS SQL Server 2019?

Question

Is user mapping required for database login in MS SQL Server 2019?

kvdb 21

Hi!

After importing a database backup from MS SQL Server 2012 into MS SQL Server 2019 I quickly found out I was not able to login into it anymore.

In SSMS there is an appropriate entry (an AD user group) under Databases -> [database name] -> Security -> Users, and up till now this sufficed to be able to login.
However, it now seems necessary to also add an entry to Security -> Logins, and then map this entity to the database user. Anyway, that was the only way I could make it work.

So, I'm wondering:

Is user mapping in MS SQL Server 2019 a requirement to be able to login into a database? (It was not in versions 2008 and 2012.)
Or is there another way to login just as a database user?
If the answer to question 1 is yes, then what is the use of being able to add a database user / group from a domain?

Thank you for your time!

Seeya Xi-MSFT 16,586 Reputation points

2021-12-24T07:14:41.907+00:00

Hi @kvdb ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya

Accepted answer

3 additional answers

Your answer

Seeya Xi-MSFT 16,586 Reputation points

2021-12-24T07:14:41.907+00:00

Hi @kvdb ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

Tom has already answered the important points. I just like to cover the last question:

If the answer to question 1 is yes, then what is the use of being able to add a database user / group from a domain?

Keep in mind that a user can have access to the instance from one or more AD groups. But then the user or an AD group may need specific permissions in a database, and therefore you may need to add the user on this level, although the user has no login of its own.

kvdb 21 Reputation points

2021-12-28T16:51:18.85+00:00

Thanks for your explanation!

Answer 2

Tom Phillips 17,771

Yes, unless your login is a sysadmin, you must map a server "login" to a database "user".

This has not changed in SQL 2019. This has always been a requirement for non-sysadmin users.

The user information is stored in the database. Therefore, when you remove or restore a database, that information is lost and must be recreated.

kvdb 21 Reputation points

2021-12-28T16:42:43.333+00:00

Thanks for your answer!

You mentioned that the 'user information is stored in the database' and also that this has not changed after SQL 2012? Do you you mean by this that a user mapping should be present in the original Security -> Logins view in SSMS for SQL Server 2012? That is not the case, but login just works! Or is this data somehow 'hidden' in SSMS 2012 and should I look elsewhere for it?

It's not that I don't see the logic or that I absolutely refuse to make these mappings :), but I just wonder why they didn't seem necessary before.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-12-28T22:00:42.427+00:00
You mentioned that the 'user information is stored in the database' and also that this has not changed after SQL 2012?

There is no difference between SQL 2012 and SQL 2019 how the user mapping is stored. Possibly, there are differences between SSMS 2012 and SSMS 18.10 displays the information, but I would not expect so. However, I can't vouch for it, because I never look in SSMS for this kind of thing. I look in sys.database_principals directly. Or I may do:

EXECUTE AS USER = 'DOMAIN\User' go SELECT * FROM sys.user_token go REVERT

To see which tokens that are associated with the user. The access may come from a group the user is member of.

As I discussed, given that users can belong to roles and AD groups, it can be quite complex to derive how a certain user has access.
kvdb 21 Reputation points

2021-12-29T17:08:37.86+00:00

Thanks again! It is indeed complicated. I'm comparing the contents of sys.database_principals from both the original 2012 database with the new 2019 database. They seem rather alike, but I'm going to have a better look.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-12-29T21:53:04.99+00:00

Since you restored the database from one instance to another, I would expect the contents to be the same, and if you want to compare differences, you also need to look at sys.server_principals who hold the logins on server level.

Also, observe that the mapping between sys.server_principals and sys.database_principals is on the SID column, not the name! For a Windows login, they should be the same on the two servers. For an SQL login they are only the same, if you actively have set them up that way.
kvdb 21 Reputation points

2021-12-30T16:36:51.94+00:00

The contents of both instances of sys.database_principals are a perfect match (fortunately only 27 records). This seems logical as well, because the SQL 2019 database is an import of a .bak file of the 2012 database.

So if sys.database_principals contains the user mappping [user_name] -> [sid], then login should have worked right away, right?
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-12-30T21:56:00.483+00:00

But keep in mind that sys.server_principals is likely to be different. That data lives in the master database, and not part of the backup you copied.
kvdb 21 Reputation points

2022-01-04T14:08:33.203+00:00

Do you mean that the data in sys.server_principals should include / reflect what is listed under Security -> Logins?

I checked out the instances of sys.server_principals of the SQL 2012 and SQL 2019 servers. I did not find in the 2012 instance any login mapped to a database user that was not mapped already. So, the logins that supposedly are mapped to some database users (with external access) are still unaccounted for.

Is there a way to find out which login is mapped to a database user, other than looking at Security -> Logins or sys.server_principals?

Thanks again for your time, much appreciated!

Answer 3

Seeya Xi-MSFT 16,586

Hi @kvdb ,

Welcome to Microsoft Q&A!
Yes. Agree with Tom.
I also want to add the third point:
https://www.sqlshack.com/sql-server-logins-users-security-identifiers-sids/

Best regards,
Seeya

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

kvdb 21 Reputation points

2021-12-28T17:11:07.793+00:00

Thanks for the background info!

Answer 4

Erland Sommarskog 121.4K MVP Volunteer Moderator

Do you mean that the data in sys.server_principals should include / reflect what is listed under Security -> Logins?

Rather the other way round. What you see in Security -> Logins should come from sys.server_principals.

Is there a way to find out which login is mapped to a database user, other than looking at Security -> Logins or sys.server_principals?

Run this in your database. Add columns as needed:

SELECT sp.name AS LoginName, sp.principal_id AS Server_principal_id, sp.SID, sp.type_desc AS login_type, 
       dp.name AS UserName, dp.principal_id AS Database_principal_id, dp.type_desc AS user_type
FROM  sys.server_principals sp
FULL  JOIN sys.database_principals dp ON dp.sid = sp.sid

NULLS to the left -> Only in database. NULLS to the right -> only in server.

kvdb 21 Reputation points

2022-01-05T11:15:11.257+00:00

Thanks! I executed this on both the 2012 en 2019 databases.

The output merely confirms what I mentioned in my OP and my comparison of sys.database_principals and sys.server_principals : None of the database users which are domain users have been mapped to a login (NULL's to the left). These users can login into the 2012 database, but not into the 2019 database.

So, in order to make login into the 2019 database possible, a mapping with a login is necessary. For this I used a domain group which includes all domain users that are database users and for which I set minimal permissions. This way users with different database permissions have the appropriate access.

It remains a mystery to me why login into the 2012 database worked without user mapping...
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-01-05T22:08:06.133+00:00

And the set of rows are the same in sys.server_principals on both instances?

By the way, what does the column authentication_type_desc in sys.server_principals say for these users?
kvdb 21 Reputation points

2022-01-07T12:06:20.37+00:00
Yes, apart from some 'system' users (I guess they are, their names being between '##') they are the same.

Those users are not in sys.server_principals (otherwise there WOULD be user mapping, right?). Did you mean 'sys.database_principals'? In that case, all users have the value 'WINDOWS' for authentication_type_desc. When I map a user, it's value is 'INSTANCE'. I still fail to see why user mapping is necessary when domain users are database users.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-01-07T22:29:45.833+00:00

Yes, you can ignore the ## names.

Yes, I meant sys.database_principals, but I think my question was flawed anyway. By chance, is this a partially contained database? You would see this in the containment_desc column in sys.databases.

Share via

Is user mapping required for database login in MS SQL Server 2019?

3 additional answers

Your answer