User appears in database after restore to new server

Question

I have a production server with a database. I restored a backup of the database to our test environment.

What I see are a bunch of users based on AD users in the newly restored database on test. These users have very specific permissions.

The difference between Production and Test is that Production does not have logins for these users and Test does.

I expected to see these users in Production by querying sys.database_principals or sysuser, but they aren't there.

My hypothesis is that production was originally created from a restore of a Test version of the database to Production, with all of these test logins. Somehow they hid and showed up again when the database was restored.

Is my idea feasible? How can I test?

Answer 1

Hi @Russel Loski ,

Do you backup the file in the production by your own account and use backfile from other account?
Is the account log in production server side has the equivalent permission as the account in the test server side?

I think the root cause is that the account in Production does not have the permission to check the sys.database_principals,sysusers, for example, the log in on the Production side as one public or others which don't have the alter any user permission, and the account log in the test server can act as sysadmin.

Note:

Any user can see their own user name, the system users, and the fixed database roles. To see other users, requires ALTER ANY USER, or a permission on the user. To see user-defined roles, requires ALTER ANY ROLE, or membership in the role.

I can reproduce your issue as next:

Production Server Side:

1. Production database backuped by YX\Administrator;
2. Use YX\Client login Production, but YX\Client is just one public one, then select from sys.database_principals, YX\Client can only check his own informations
   

Test Server Side:
YX\Client as sysadmin in Test Server side, then this account restore the backupfile and use the sys.database_principals, this account can see all the information.

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

My guess is that the backup file has more than one backup in it, and when you don't specify the WITH FILE option, RESTORE will read the oldest backup.

Use RESTORE HEADERONLY to see how many backups there are in the backup file.

Answer 3

There is only one backup in the backup file (the file name has the time to the millisecond).

Answer 4

@m : The account that backed up the database on Production is a sysadmin on Production but isn't a login on Test. The account that restored the database to Test is a sysadmin on Test but isn't a login on Production. I am sysadmin on both Production and Test. I'm not sure of the relevance since the source of the backup is not showing users that are showing in the restored database. If the restored database didn't show users that are showing in the source, I could imagine the relevance of this.

I'm sysadmin on both Production and Test. Therefore shouldn't I be able to see all of the users that are in the database using sys.database_principals?

I am not an Active Directory person, so I don't know if the following is possible. The owners of the database do not want these test users that are appearing in Test to have access to production. Is there a way to block that access in Active Directory or some other place? And would that blocking cause these users to not show in database_principals?

Answer 5

Hi @Russel Loski ,

I'm sysadmin on both Production and Test. Therefore shouldn't I be able to see all of the users that are in the database using sys.database_principals?

If you are sysadmin, you can check all the users that are in the database using sys.database_principles.
sysadmin :Members of the sysadmin fixed server role can perform any activity in the server.

Is there a way to block that access in Active Directory or some other place? And would that blocking cause these users to not show in database_principals?

I do not think there is one way can achieve that.
Any user can see their own user name, the system users, and the fixed database roles. To see other users, requires ALTER ANY USER, or a permission on the user. To see user-defined roles, requires ALTER ANY ROLE, or membership in the role.

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.