0

All databases from a server have just been migrated to a new server. It looks as if some users were carried across and some weren't, I'm not really sure as I did not know the server very well.

One user who was on the old server has requested a new windows login on the new one. When I go to create this login, and map it to a particular database, the 'db_owner' role is already checked, whereas for other databases it is not? The user has only requested read only.

Obviously I can just uncheck this but how is it that this is the case? Were they an owner of the previous database and someone has cleared out the users and created them anew and SQL remembers this person was a user? Or have all new users automatically been set to have db_owner granted to them? Is there anything I can query to see more information on this?

Thanks

  • 1
    Please check this thread - stackoverflow.com/questions/18085351/… – Learning_DBAdmin Nov 25 '19 at 12:58
  • How does that relate to trying to create a brand new login then when you create the user mapped to that login the user automatically being given db_owner permission? – jack Nov 25 '19 at 13:25
  • The user did not already exist on the database, but thanks for your response. Learning_DBAdmin was correct - there was no owner listed under properties > files, i changed this to sa and it now does not automatically populate new user with db_owner. – jack Nov 25 '19 at 16:31
1

From Comments:

This issue could occur when there is no owner listed under properties --> files. Once you populate the owner with a valid login, this issue is gone and db_owner is not granted automatically.

Same can be done using sp_changedbowner.

Please mark it as accepted answer so this could help someone else facing this or similar issue on DBA.SE forum.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.