Need user to Create/Attach DB and then be immediate dbowner of the new DB

Question

Need user to Create/Attach DB and then be immediate dbowner of the new DB

Viewing 7 posts - 31 through 36 (of 36 total)

You must be logged in to reply to this topic. Login to reply

Paul Bradshaw SSC Eights! Points: 949 More actions · Answer 1

jared-709193 (10/4/2011)
Ok, I just did some testing. I believe that the login you are using to attach/restore the database was never given access to the original database 🙂
Attach/restore the file somewhere.
Login as sa and map the login you are using to attach to be db_owner of the attached database.
Detach/backup the modified db
Attach or restore using your login for this
The problem is that on the properties of the database, it shows the login that you used to create it. However, that login is NOT a user on the attached/restored database (this is stored with the backup or .mdf file and replaces any users that were created on your new db). So that user has to be added to the db file or db backup first before attaching or restoring. This is why creating the database without attach works fine. The user that creates the db is added as a database user. But with the attach or restore, that user gets overwritten with the users that were on the original.
I did this, and it worked fine for me.
Thanks,
Jared

Okay, you wrote this while I was writing my reply above...

In other words, we would have to tell our customers to create a specific login on their SQL Server, with a specific name. That "hard coded" name would then be "hard coded" as a user in our "model" database. And at attach/restore time, that user would "magically" be re-connected to the same-named login on the target server? Even though the SPIDs might differ?

I'm wondering if there still isn't a chicken-and-egg issue, where the login woud be required to run the "Auto-Fix" stored procedure, but wouldn't be able to due to the fact that the connection is broken to begin with 🙂

I'll take your word for it that it works on the same server, but I'll have to test moving a database from one server to another... that MIGHT be away around it, but we had sort of hoped on being able to tell customers to just create a user (of their own naming) and just give it the required permissions. Having every customer use the same login/user is still better than requiring everyone use sa or sysadmin, but not as good as just allowing them to specify whatever login/user they want (and preferably not having to have them create a special DB "user" at all).

The part that still confuses me (and I'm easily confused, I admit) is that when I do all this with a sysadmin account, there is no user in the DB associated with it, and it all just works. I guess it's because they're "db_owner of all databases" at a higher level (the server) so it doesn't matter what's in the actual database. I just wish that worked with creating databases... the server registered this login as the 'owner' so even though the DB was restored or attached, the login would be able to boot-strap itself into the DB.

Ah well. I'll try your method and report back. Thanks!

george sibbald SSC Guru Points: 104210 More actions · Answer 2

could you just post the results of sp_helpuser (run in the database) immediately after restoring\attaching it.

---------------------------------------------------------------------

Jared SSC Guru Points: 61793 More actions · Answer 3

I'm not sure what is wrong with the customer using sa. Microsoft does this when setting up Great Plains, BigFix does this for its database... This is usually because they are creating databases, creating permissions on the database, etc. The application SHOULD have sysadmin rights to MSSQL.

Thanks,

Jared

Jared
CE - Microsoft

george sibbald SSC Guru Points: 104210 More actions · Answer 4

ok, now I am confused. Are you using a SQL authenticated account?

If I try and do this with a SQL account that does not have sysadmin rights I cannot break out of SQL and access the file system at all, so I cannot even restore or attach database files, which makes sense.

You must be using a windows account, grant that account the necessary privileges to the file system.

When this is going to outside customers, give a backup (preferably) or files to attach, a script to create logins and make amendments in the database, and let their DBA run it.

---------------------------------------------------------------------

Paul Bradshaw SSC Eights! Points: 949 More actions · Answer 5

george sibbald (10/4/2011)
ok, now I am confused. Are you using a SQL authenticated account?
If I try and do this with a SQL account that does not have sysadmin rights I cannot break out of SQL and access the file system at all, so I cannot even restore or attach database files, which makes sense.
You must be using a windows account, grant that account the necessary privileges to the file system.
When this is going to outside customers, give a backup (preferably) or files to attach, a script to create logins and make amendments in the database, and let their DBA run it.

I'm using "Mixed SQL Server Authentication". I created a SQL Server login. There is no windows account associated with it.

I GRANT the login the CREATE DATABASES permission (from a sysadmin login of course... same one I used to create the login to begin with). For example... login as a sysadmin, and run these steps:

CREATE LOGIN TestUser WITH PASSWORD = 'TestUser', CHECK_POLICY = OFF

GRANT CREATE ANY DATABASE TO TestUser

Now that user can create databases, attach databases, and restore databases. At least in my experience.

Of course, once they attach or restore, they're locked out of the database until a sysadmin can fix it up.

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 6

Paul,

This is a very interesting issue. When you get it completely figured out, it would make a great article of the dos and don'ts for getting this working if you're up for it.

Paul Bradshaw SSC Eights! Points: 949 More actions · Answer 7

jared-709193 (10/4/2011)
Ok, I just did some testing. I believe that the login you are using to attach/restore the database was never given access to the original database 🙂
Attach/restore the file somewhere.
Login as sa and map the login you are using to attach to be db_owner of the attached database.
Detach/backup the modified db
Attach or restore using your login for this
The problem is that on the properties of the database, it shows the login that you used to create it. However, that login is NOT a user on the attached/restored database (this is stored with the backup or .mdf file and replaces any users that were created on your new db). So that user has to be added to the db file or db backup first before attaching or restoring. This is why creating the database without attach works fine. The user that creates the db is added as a database user. But with the attach or restore, that user gets overwritten with the users that were on the original.
I did this, and it worked fine for me.
Thanks,
Jared

Okay, I verified that this DOES work if you're staying on the same server, but DOES NOT work if you're moving from one server to another (i.e. the DB your attaching or restoring comes from a different server).

However, I also found that if, on the target Machine, you create a login with the *specific* name and SID as on the source Machine... then it works.

This MIGHT be an answer: just require that a login be added to the SQL Server where the install will take place that meets those two specific criteria (plus has the CREATE DATABASE permission). Then things seem to work.

This seems to be an awful lot of hoops to jump through though.

I guess I'm wishing for an option on RESTORE and CREATE FOR ATTACH like "WITH CLAIM_OWNERSHIP" that would just automatically create a user and link it to the current login as required, so that the login that created the database and is listed as the 'owner' would STILL be the owner with owner permissions in the DB after the Restore/Attach completed.

Anyway, thanks for all the ideas!