• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1847
  • Last Modified:

Can't determine owner of 'DYNAMICS' database

I am able to successfully create a user in Great Plains, but when I try to select a Company in User Access, I get the following error:

The stored procedure smUsrCmpnyAccssChckAccssCHG returned the following results: DBMS: 0, Great Plains: 20293.

If I click More Info, it says this:

smUsrCmpnyAccssChckAccssCHG: Unable to determine owner of DYNAMICS database.


Any ideas? Thanks!
0
Tharo_Systems
Asked:
Tharo_Systems
  • 7
  • 4
  • 4
  • +3
2 Solutions
 
twoboatsCommented:
Is the guest user permitted in the master database?
0
 
Tharo_SystemsAuthor Commented:
When I select the Permissions tab from the properties window of the master database in Enterprise Manager, there are 2 users/roles: public and guest. None of the boxes are checked for either of these. Is that what you wanted to know?
0
 
CragCommented:
What happens if you run the following query:

sp_helpdb DYNAMICS
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Tharo_SystemsAuthor Commented:
It shows my Windows Domain User Name as the owner. I don't think that's right. What should it be? dbo? sa? How do I fix it? Thanks!
0
 
CragCommented:
I've no idea what the app is expecting to be the owner.
You can change the owenr using the following command
Set the database required to be the current one.

sp_changedbowner <newowner>
0
 
Tharo_SystemsAuthor Commented:
It says 'The proposed new database owner is already a user in the database'
0
 
CragCommented:
This is a beaut!
I'm not sure what impact changing the owner is going to be as it seems crazy that the app needs to know.
Can you run the SQL Profiler against the session and try and identify the statement that it executes int he smUsrCmpnyAccssChckAccssCHG SP that causes the error code?
you may also be able to find it by reviewing the SP directly
0
 
Tharo_SystemsAuthor Commented:
I can run the Profiler, but I don't know what any of it means. Same with the SP. Is there a way to create a new database with the correct owner and import all the tables into it?
0
 
twoboatsCommented:
It says 'The proposed new database owner is already a user in the database'

What user are you changing it to? If it exists in the db, can't you just drop it and then change the owner?
0
 
CragCommented:
It's not easy to walk you through the profiler I'm afraid and i'm a bit stumped as to why the product needs to know the owner of the database.
0
 
Tharo_SystemsAuthor Commented:
There are 4 databases. DYNAMICS, and one for each of 3 companies. The 2 companies that are not having issues, have 'sa' for owner, so I tried setting it to that. I can't drop 'sa'.
0
 
twoboatsCommented:
Weird.

Have just taken a database owned by a domain account and run

sp_changedbowner 'sa'

with no problems.

So. Go through the users in the database, and see for each db user what the login is - you may find that the sa login is already mapped to a db user (though the db user might not be called sa). If so, remove that db user.

0
 
twoboatsCommented:
btw

use dynamics
sp_helpuser

will list the users and their logins
0
 
Tharo_SystemsAuthor Commented:
UserName: dbo; GroupName: db_owner; LoginName: sa; DefDBName: master; UserID: 1; SID: 0x01

The rest of the users listed look correct. SA is not listed anywhere else.
0
 
Tharo_SystemsAuthor Commented:
OK, in a test environment, I created a user called 'DYNAMICS' in SQL Server. Not in Great Plains. I then ran the SQL query: sp_changedbowner 'DYNAMICS' and now I can grant access to users! I'm afraid this will affect something else if I try it on the live system. Any advice or thoughts on this?
0
 
nolaitservicesCommented:
The DYNAMICS database and the company databases should have DYNSA as their owner.

Verify or change the owner of your databases. To do this, follow these steps:1. To find out who the owner of your databases is, run the sp_helpdb SQL script in Query Analyzer.

Note If the owner of your DYNAMICS database and company databases is something other than DYNSA or "sa," you will receive the 20293 error.  
2. Change the owner of the DYNAMICS database and all the company databases to sa first, and then to DYNSA.

Run the following SQL script in SQL Server Management Studio (SQL 2005), Query Analyzer (SQL 2000), or the Support Administrator Console (MSDE 2000) against the DYNAMICS database and all the company databases.
sp_changedbowner 'sa'
 
3. After the database owner is set to "sa," set it to DYNSA. Run the following script in SQL Server Management Studio, in Query Analyzer, or in the Support Administrator Console.
sp_changedbowner 'DYNSA'
 
4. To verify that the database owner has changed to DYNSA, run the sp_helpdb script again.  




If you receive the error - 'The proposed new database owner is already aliased in the database.' occurs when  trying to use the stored procedure 'sp_changedbowner DYNSA' to change the owner of a SQL database.

You will need to run the following statement in the SQL Query Analyzer, against the database you were trying to change:

sp_dropalias 'DYNSA'

After this, you should be able to run the 'sp_changedbowner DYNSA' stored procedure.

0
 
Victoria YudinOwnerCommented:
While setting the db owner to 'sa' will usually work for most things, for everything to work correctly the owner for all GP databases, including DYNAMICS and all the company databases, should actually be DYNSA, which is a built in user that automatically gets created when GP is installed.
0
 
jerrychrismanCommented:
The sp_dropalias may not work in most environments.
Use the sp_dropuser then the sp_changedbowner
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 7
  • 4
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now