January 19, 2012 at 1:26 pm
SQL2008 R2
We are running Microsoft Dynamics Great Plains on it.
We commonly copy our 'LIVE' SQL database into our TEST database.
Part of our Procedure is to run this script against the TEST database before we run another script to search and replace CMPNYID values:
sp_changedbowner 'DYNSA'
When I do.......I get this error message.
Msg 15110, Level 16, State 1, Line 1
The proposed new database owner is already a user or aliased in the database.
When I right click the database the owner is 'sa' - which is different than my SQL2005 install where all my Great Plains database owners should be DYNSA.
I know for a fact that in Great Plains - DYNSA should be the owner......
Why would I get this message?
January 19, 2012 at 3:33 pm
SQL doesn't allow you to let a database user become THE database owner, or for the database owner to be made an explicit database user. This is different to being a member of the db_owner role.
If the DYNSA user isn't explicity a database user it may be that the logon's SID matches the SID of an orphaned user on the database. As I understandit SQL is actually using the SIDs to do the checks, not the names. Try first fixing or removing any orphaned users to see if this resolves the issue. If not then you may have to first remove the DYNSA permissions on the database before you can make the db owner change.
Having said this I can't see why GreatPlains must be owned by DYNSA. I've supported GreatPlains at a number of companies and the databases have been owned by sa without issues.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
January 20, 2012 at 4:51 am
Leo - Thanks for the reply.
I know DYNSA is not an orphaned user. DYNSA user is automatically created when Great Plains is installed on a SQL Server, much like 'sa' is created when SQL is installed.
In Great Plains, if a user creates a new company database (using GP Utilities) the database is automatically assigned DYNSA as the owner. DYNSA is the GP Version of 'sa'.
I certainly agree that the DB owner is not normally significant.........however, I'm getting an error when I run a Great Plains SQL script that changes 'CMPNYID' field (among others) in the databases that are being backedup and restored.
MBS states that changing the owner is the fix. (No idea why).
I think the DB owner issue is related to how my IT group moved these databases from 1 server (we had a TEST environment) to the Production Server.
MBS/GP prescribes GP be installed - GP be used to create the Databases and then the databases (with DYNSA as owner) be restored from backups copied over from the TEST server. My IT did not follow that course.
I think the real issue - why can't the sp_changedbowner be executed without error? I've run it many times without error.......
January 22, 2012 at 12:28 pm
As long as DYNSA is a user in the database you can't make it the database owner. You will firts have to delete the user from the database, then run the sp_changedbowner script.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
January 23, 2012 at 7:21 am
FWIW, I haven't found support for GP to be top notch. They used to insist we run GP under "sa" until we pointed out there wasn't a reason for this. We could create logins, and GP would pick them up and map them.
There are likely 1 of 2 things here. One is a check for the db owner, in which case you'd need to change the owner. This may involve scripting the rights, dropping the user, changing owner, adding the user. I'd have to research a bit to see how to get around that.
The 2nd thing might be some permissions. A detailed trace when you get the error might track it down. Likely GP is doing something strange here.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 23, 2012 at 1:34 pm
I assume you are doing what I used to do for this: Take backup of productionCompany database, restore to test database, run replace script to replace the company name.
The issue you may be getting is if you did not restore the backup properly. Have you tried doing this again?
Jared
CE - Microsoft
January 23, 2012 at 1:48 pm
Bron Tamulis (1/20/2012)
Leo - Thanks for the reply.I know DYNSA is not an orphaned user. DYNSA user is automatically created when Great Plains is installed on a SQL Server, much like 'sa' is created when SQL is installed.
Do you completly understand what an "orphaned" user is?
On Server A, DYNSA is created with a SID of 00X00, and granted access to the database Dynamics.
On Server B, DYNSA is created (or, probably in your case, it already exists) and has a SID of 11x11. It is also granted access to the database Dynamics.
When the Dynamics database is restored from A to B, the user DYNSA is "orphaned". It has a SID of 00x00, but the SID on Server B is 11x11.
You probably need to run this first:
EXEC sp_change_users_login 'UPDATE_ONE', 'DYNSA', 'DYNSA'
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 23, 2012 at 1:56 pm
Michael L John (1/23/2012)
Bron Tamulis (1/20/2012)
Leo - Thanks for the reply.I know DYNSA is not an orphaned user. DYNSA user is automatically created when Great Plains is installed on a SQL Server, much like 'sa' is created when SQL is installed.
Do you completly understand what an "orphaned" user is?
On Server A, DYNSA is created with a SID of 00X00, and granted access to the database Dynamics.
On Server B, DYNSA is created (or, probably in your case, it already exists) and has a SID of 11x11. It is also granted access to the database Dynamics.
When the Dynamics database is restored from A to B, the user DYNSA is "orphaned". It has a SID of 00x00, but the SID on Server B is 11x11.
You probably need to run this first:
EXEC sp_change_users_login 'UPDATE_ONE', 'DYNSA', 'DYNSA'
You are assuming these are on separate instances 🙂 My guess is that they are not as GP accesses the 1 instance, but multiple databases create multiple companies.
Jared
CE - Microsoft
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply