User appears in database after restore to new server

Russel Loski 421 Reputation points
2020-11-24T20:00:44.603+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,957 questions
0 comments No comments
{count} votes

Accepted answer
  1. m 4,271 Reputation points
    2020-11-25T03:30:07.873+00:00

    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
      42531-202011125yxclient.png

    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.
    42462-20201125allinfor.png

    BR,
    Mia


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

    1 person found this answer helpful.
    0 comments No comments

5 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 112.7K Reputation points MVP
    2020-11-24T22:28:32.5+00:00

    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.

    0 comments No comments

  2. Russel Loski 421 Reputation points
    2020-11-25T00:25:25.167+00:00

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


  3. Russel Loski 421 Reputation points
    2020-11-25T14:30:06.67+00:00

    @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?

    0 comments No comments

  4. m 4,271 Reputation points
    2020-11-27T05:32:53.927+00:00

    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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.