I have a database that has been restored into my environment from a production website for development. The original database was SQL Server 2016, and it was originally 2016 in my development environment too. I used Red Gate SQL Compare/Data Compare to migrate it to 2014 for maximum compatibility across environments.
In both the restored SQL Server 2016 and 2014 environments there are two users listed but have an icon indicating that they are missing (red X over them).
If I run a query to list all users in the database, they indeed appear to be missing. Example queries are below.
select suser_sname(owner_sid) as 'Owner', state_desc, *
from sys.databases
order by [name]
select *
from master.sys.server_principals
order by [name]
The two user accounts in question are INFORMATION_SCHEMA and sys. These appear to be built-in user accounts, per MS documentation below.
Why would these accounts be missing after restoring the database?
Do I need to add them? If yes, how do I do that safely? According to the steps provided in the documentation below, they aren't listed as orphaned accounts.
What is the risk of not recreating these user accounts?
The red X for the user icon in SSMS Object Explorer indicates the user doesn't have CONNECT permission to the database. This is expected for the sys and INFORMATION_SCHEMA database principals as those are used solely for ownership of the corresponding schemas with the same name.
The queries in your question return server-level principals so these database users, which are not mapped to logins, are not returned. You will see a sid value NULL for these users when you query sys.database_principals.
So the bottom line is that this is normal (database restore or not) so no action is needed.