SQLTeam.com | Weblogs | Forums

Cannot Create or Map Existing User to new Database


#1

This is very simple folks but things have changed since I last installed sql.

I am not under a Domain by the name: AzureAD\Erik

I'm using windows 10 enterprise edition with SQL Server 2016

I use to be able to detach my databases, in previous SQL Servers | Reattach them to say 2010 from 2008 SQL Servers and Map existing user to the newly created or existing attached databases but this is not the case now.

For one I cannot seem to give a specific user the master privileges as before because the new domain user wants to hog everything for itself.

I am using Office365 Enterprise E5 edition so that puts my machines with in that domain; however, they're must be new tips and tricks I am missing.

Can someone tell me how to map an existing login user to a newly created database with out the error in the photos.

I really like 2016 But it could take me until 2020 learn all the newly create features.

So, back to my very basic questions' it is two questions here

  • How to create a login user that has the same authorization and authentication as the default user SQL Server always creates when a new database is created?
  • How to Map an existing user to newly created database?
  • And keep in mind that if I attach a database with the created login user it automatically adds the login user to the database and also keep this specific user as the owner of the database without an error.

**Please see the image, YOU MUST DOWNLOAD THE PHOTO THEN LOOK AT IT IN PAINT

So, any help out there is awesome!

Regards,

Erik


#2

For each step you take in SSMS, would you please click the script button at the top of the detail window and paste the scripts generated here?


#3

Let's assume you want your login/user to be 'supererik' and that you want that user to be the owner of the database. In that case - you have 2 options:

  1. Make user supererik the owner as you have done in the above pictures. That user is now the owner of the database and has full privileges as the owner to everything in that database.

  2. Make sa the owner of the database - then add supererik as a user to that database and add that user to the db_owner role.

Personally, I use the second option so that I can always remove a login and user without affecting the database. Your problem here is simply thinking that the owner of the database must also be mapped to a role in that database - which is not correct. The owner of the database is mapped as the user 'dbo' in that database which gives that user full control over that database.


#4

This is a precise and great outline of what I should do and cleared up some missunderstandings.

Thank you!