November 19, 2014 at 2:03 pm
SQL Server 2008 R2 SP3
A VAR is trying to install a new reporting tool for MS Dynamics on one of our SQL servers. The installer is failing on an error that seems to be caused by the ownership of the db_backupoperator schema. SQL runs under an AD account, and that account is getting assigned ownership to the db_backupoperator schema.
DB's on the server that have been moved there from another server all have the db_backupoperator as the owner, but newly created db's come up with the AD service account in that spot. I suspect the server where the moved db's originated were running SQL Server under the default accounts, but I don't know that for certain.
So...
What determines the ownership of schemas in a newly created database?
If it defaults to the service account for the server install, how can that be overridden?
If it's a matter of setting the service account back to something more generic, what would that be?
Or, anything else anyone has to offer?
November 19, 2014 at 2:19 pm
One way it's determined, of course, is by the ownership in the Model DB. In this case it was set to the service account. When I set it back to db_backupoperator the newly created db's have that as the schema owner (predictably enough). Now the question might be, how did it get set to the service account in Model, and will changing it there break anything?
Hmmmm
November 19, 2014 at 2:23 pm
Someone must have changed it in model. As to whether it will break anything? No way to know. It's unlikely, but be prepared to respond to this and let your support people know you are changing this.
I've rarely seen issues with this in schemas. Job and db owners sometimes cause issues with processes that expect things to be sa, but I would guess you're OK. I'd probably just make the change and then fix things if they break.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply