We're recently purchased some new software, which requires its own SQL Server database. Working with the vendor they also needed to have a new SQL account created which would be a part of the db_owner database role membership. Creating new SQL accounts isn't something we do often (I cannot remember when I last created a new SQL account), so I made a mistake when I set the new user up. Here's what happened:
First, I created the new database. After setting it up, assigning the .mdf and .ldf files to their appropriate places, I then created the new SQL account, and in the property page for creating the new user, I checked db_owner under the "Schemas owned by this user", rather than "Database role membership".
Later I realized my mistake, but I'm not sure how to fix it. First of all, what account normally has ownership of the db_owner schema? I would assume it would be the sa
account, especially for a new database like this one, but I want to ask anyway.
Second, how do I reassign the db_owner schema from this new SQL account to the account it should really be owned by?
Connect with me on LinkedIn