Wednesday, July 14, 2010

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/

5 comments:

Robert Cavill said...

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.

Anonymous said...

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

RS said...

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

Unknown said...

Hello,

I am working with a client and upgrading 9.0 to 2010. I set them up in a virtual environment to test the upgrade first before doing it in the live system. I noticed the DYNSA user is not even present at all, in master db, DYNAMICS or the company db's. Is there a stored procedure or sql script I can run to create this user? Or can I just add it in via the management studio? If I create it and set the dbo to DYNSA do I still need to create DYNSA in DYNAMICS, as well as the company DB's? Thanks!

Mariano Gomez said...

@Anonymous:

I have used the following script in the past:

/****** Object: Login [DYNSA] Script Date: 02/04/2012 21:54:45 ******/
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'DYNSA')
DROP LOGIN [DYNSA]
GO

/* For security reasons the login is created disabled and with a random password. */
/****** Object: Login [DYNSA] Script Date: 02/04/2012 21:54:45 ******/
CREATE LOGIN [DYNSA] WITH PASSWORD=N'= 5kG èÇ„CŽü"Ô °V¤%óug ÁÓ_·', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

EXEC sys.sp_addsrvrolemember @loginame = N'DYNSA', @rolename = N'securityadmin'
GO

EXEC sys.sp_addsrvrolemember @loginame = N'DYNSA', @rolename = N'dbcreator'
GO

ALTER LOGIN [DYNSA] ENABLE;
GO

USE master;
GO

ALTER AUTHORIZATION ON DATABASE::DYNAMICS TO DYNSA;
GO

USE DYNAMICS;
GO

DECLARE @interid VARCHAR(10);
DECLARE @sqlstmt NVARCHAR(500);

DECLARE c_company CURSOR FOR SELECT INTERID FROM SY01500;

OPEN c_company;
FETCH NEXT FROM c_company INTO @interid;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlstmt = 'ALTER AUTHORIZATION ON DATABASE::' + RTRIM(@interid) + ' TO DYNSA';
EXEC sp_executesql @sqlstmt;

FETCH NEXT FROM c_company INTO @interid;
END

CLOSE c_company;
DEALLOCATE c_company;

GO

/* End of script */

Note that you will need to reset the DYNSA password when done.