question

Blackhawk-17 avatar image
Blackhawk-17 asked

CREATE DATABASE... FOR ATTACH Ownership Issue

Okay, I'm trying to be a good security-minded DBA but I'm having problems with allowing a user to create databases and then accessing them. The user belongs to the DBCREATOR server role and can attach an MDF and LDF to create a dB but then they cannot access it. They are not made the DBO nor do they have a user in the new dB. I'm sure I'm missing something simple here but I'm just back from vacation and not quite into the swing of things :) Your assistance with this is appreciated. EDIT ---- ---------- I've submitted this as a bug: [CREATE DATABASE...FOR ATTACH Issue][1] [1]: https://connect.microsoft.com/SQLServer/feedback/details/687522/dbcreator-role-doesnt-update-owner-with-create-database-for-attach-statement
sql-server-2008createattachdbo
10 |1200 characters needed characters left characters exceeded

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

1 Answer

· Write an Answer
BradHarker avatar image
BradHarker answered
Not too sure how you have the roles configured but there is a major area of concern here that should be pointed out first. Any user that is given the dbCreator server role has the ability to also drop/alter any other database. This means any not just their own. It is a better practice to give the user the CREATE ANY DATABASE server permission instead so then they can only drop/alter their own databases and not all of them. There may be an issue with the public database role not being able to access the databases that are being created; something in their security or related to the roles and permissions assigned to the users would cause this issue. I would start by checking out the user in and roles in these tables: *sys.server_role_members, sys.server_principals, sys.syslogins, sys.sysusers*
10 |1200 characters needed characters left characters exceeded

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

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.