PAC 1 - Packager does not add users to builtin roles

Brian Donahue

• Date: 13 Oct 2004
• Versions Affected: 1.0

If you package a database in Packager, and the package is not set to upgrade an existing database, you may notice that database users are not added to default built-in roles in the new database that the Package creates. This behaviour is contrary to what SQL Compare does. SQL Compare will add users to roles such as db_datareader in its' migration script.

In SQL Compare, the built-in roles are compared. When you compare two databases, the built-in roles exist in both databases, so all that’s scripted are changes to these roles. SQL Compare will see that user x is not part of db_owner and script an sp_addrolemember for user x to the role. SQL Compare will not try to create db_owner because the role exists in both databases being compared.

The reason Packager filters this out is because when you package a new database, you are not comparing two databases but creating a script to generate a new one. Packager effectively does this by comparing your database to a blank one. When SQL Compare creates the migration script, it would normally try to create the built-in roles because they don’t exist in a NULL database. The script would fail because these built-in groups are created automatically with the CREATE DATABASE SQL command that Packager issues to create a new database, so Packager automatically ignores built-in database roles.