SQL Server database missing INFORMATION_SCHEMA and sys users

4szc88ey  于 5个月前  发布在  SQL Server
关注(0)|答案(2)|浏览(84)

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.

https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/principals-database-engine?view=sql-server-2017#informationschema-and-sys-users-and-schemas

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.

https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/troubleshoot-orphaned-users-sql-server?view=sql-server-2017

What is the risk of not recreating these user accounts?

tuwxkamq

tuwxkamq1#

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.

yduiuuwa

yduiuuwa2#

Try:

use <restored database name>;
 
 exec sp_change_users_login 'report';

To list all (database) users that are orphaned from (server) users after a restore to a new server. Information on this stored procedure can be found here:

sp_change_users_login (Transact-SQL)

You can then use:

exec sp_change_users_login 'Auto_Fix', 'missinglogin'

to re-link a database where a server user already exists with the same name, or

exec sp_change_users_login 'Auto_Fix', 'missinglogin', null, 'C!$#$!$#@!@!@#%^&^&@!'

to recreate at the server level and provide its new password e.g 'C!$#$!$#@!@!@#%^&^&@!.

Note once that is done you may have to

  • modify the server users settings for password expiration and complexity
  • do the same for other restored databases

相关问题