DYNSA

There are a number of good blog posts in the community explaining the differences between sa, Microsoft SQL Server’s defacto administrative user; and DYNSA, Microsoft Dynamics GP defacto administrative user. In reading a number of these articles I realized one thing: they address little about the DYNSA user.

So what’s DYNSA?

You only have to read back a few lines to obtain this answer, however, I want take another direction. Let’s start by saying that in Microsoft SQL Server, every database is created with a dbo user. Take for example this blank database created using SQL Server Management Studio:

The dbo is a user that has implied permissions to perform all activities in the database. Any member of the sysadmin fixed server role (like sa for example) who uses a database is mapped to the special user inside each database called dbo. Also, any object created by any member of the sysadmin fixed server role belongs to dbo automatically.

For example, if user DYNSA is a member of the sysadmin fixed server role and creates a table RM00101, RM00101 belongs to dbo and is qualified as dbo.RM00101, not as DYNSA.RM00101. Conversely, if DYNSA is not a member of the sysadmin fixed server role but is a member only of the db_owner fixed database role and creates a table RM00101, RM00101 belongs to DYNSA and is qualified as DYNSA.RM00101. The table belongs to DYNSA because he did not qualify the table as dbo.RM00101… and hear is the catch! Because DYNSA is not created as a member of the sysadmin fixed role (otherwise we would have a lot of angry database administrators), it must be associated to fixed database role db_owner, hence all object creation operations performed from Dynamics Utilities must qualify the object creation preceeded by “dbo.”.

The dbo user cannot be deleted and is always present in every database.

Only objects created by members of the sysadmin fixed server role (or by the dbo user) belong to dbo. Objects created by any other user who is not also a member of the sysadmin fixed server role (including members of the db_owner fixed database role):

•Belong to the user creating the object, not dbo.

•Are qualified with the name of the user who created the object.

This explains two things:

1) Only sa can perform the first time installation of Microsoft Dynamics GP, since this is when all databases, database objects, and the DYNSA user are created. During installation, DYNSA is made a member of the db_owner fixed database role.

2) In lieu of the sa user, Microsoft Dynamics GP system administrators can use DYNSA to perform any upgrade or maintenance activities. Since DYNSA is the defacto database owner, then it can perform any activities related to that database. This is the reason why you should ensure that DYNSA remains the database owner prior to initiating any upgrade activity. The following script should help with this:

1. Run the following making sure that DYNSA is the database owner:

sp_helpdb DYNAMICS;
go
sp_helpdb %COMPDB%;
go

2. If the above returns anything different than DYNSA, use the following script to set the database owner to DYNSA:

use DYNAMICS;
go
sp_changedbowner 'DYNSA';
go
use %COMPDB%;
go
sp_chagedbowner 'DYNSA';

In the above scripts, replace %COMPDB% for your company database name on SQL Server.

Related Articles:

Microsoft Dynamics GP 10 POWERUSER role vs Microsoft SQL Server sysadmin role

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Advertisements

3 Responses to DYNSA

  1. Even though DYNSA can create new companies or perform upgrades, as it is assigned the Fixed Server Role of db_creator, it cannot be used within DynamicsGP to create or modify users.To allow DYNSA to also control users, add the securityadmin fixed server role.

  2. Anonymous says:

    You seem to have a slight confusion between schemas and users (admittidly, prior to 2005, they had a 1:1 correspondance).The 'dbo' in 'dbo.tablename' references the dbo schema, which is in turn (usually) owned by the dbo user. (A schema doesn't have to be owned by their namesake user.)But, now, it's quite possible (and normal) for a user to not have a schema specifically for them. Nowadays, objects created by a user will be in that user's default schema (an explicit attribute set on each user) unless otherwise qualified.– Shann

  3. RS says:

    The dbo in dbo.RM00101 refers to the Schema and not the user itself. If you download AdventureWorks for SQL 2005, I believe there are some good examples of this. Thanks, RS

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: