up vote 0 down vote favorite
share [g+] share [fb]

I've got a SQL Server 2005 Express database on a Vista system (DotNetNuke) with the dbo user connected to a login of foo. This is the account that DNN uses to log in to the db.

I made a backup of the db and restored it on a Win7 SQL Server 2008 R2 Express. The dbo user is now no longer associated with a login. I can't find any way to get it reassociated with a login. So:

1) Should I struggle forward with trying to get dbo reconnected to a login? If so, how does one do this?

OR

2) Should I just create a new user, associate it with db_owner, make the default schema dbo, and then associate this new user with the login that my DNN app pool will use?

Option 1 strikes me as the better solution, but I just don't know how to get there or even whether it's possible.

link|improve this question
feedback

1 Answer

up vote 0 down vote

Applications should not use logins for users that are assigned the db_owner role, to begin with.

What exactly is the problem you are trying to solve here ? dbo is not a database user as such - it is an internal construct that is not usable from the outside. (more technically: dbo is the default owner of schemata that have not been assigned one explicitly)

If the application needs db_owner access for login X, just create one.

link|improve this answer
I'm not entirely sure how the login became associated with the dbo role in the first place. My guess would be that DNN created the database through the foo login it was given and that was that. I guess the problem I'm ultimately trying to solve is moving this whole website to a new machine and having it actually work correctly with a minimum of stress, fuss, and bother. It sounds like, in your estimation, that a new user with db_owner should function equivalently to the login that was associated with dbo, right? Should it be necessary to set the default schema on this new user to dbo? – Kevin Donn Dec 15 '11 at 16:56
feedback

Your Answer

 
or
required, but never shown
discard

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