question

eocula avatar image
eocula asked

User appearing after a db-restore, even after user is deleted before the db-backup

Hi, I have a strange behavior :

we are moving to a new sqlserver (sql2016, let me call it Server-B), from a sql2012 (Server-A), and I have issue with a particular DB

  1. I Executed a backup of DB on Server-A
  2. Then executed a restore of DB on Server-B
  3. I noticed there is a user (User-A) which owns a schema. And I want to get rid of this User-A (as that person left the company)

So, on Server-A :

  1. I changed ownership of that schema from User-A to dbo
  2. I deleted User-A on Server-A, DB
  3. I executed a backup of DB on Server-A

On Server-B :

  1. I restored the DB again
  2. Checked in the list of users, and User-A is appearing again, and it still owns that schema !

I do not understand this. Am I missing something ?
Please help. Many thanks

restoreuserownership
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM ·

Is there a trigger somewhere? Is there a corresponding login for the user on Server-B already?

0 Likes 0 ·
eocula avatar image eocula ·

@JohnM : there is no trigger. And there is no login for that user on Server-B

0 Likes 0 ·
JohnM avatar image JohnM ·

Is there an application tied to the database that could be putting the user back? I might try to put a trigger on the database or an extended event to see if something is putting the user back after you remove it.

0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.