Change Database Role Owner

There are times where you might be moving a DotNetNuke® database from one development machine to another.  In these cases, you might also use the data SQL user for your development.  Depending on how you move your databases, this user will still exist in the database itself, but will not exist in the new SQL Server instance.  You might think, “That’s easy! I will just add the missing user.”  Unfortunately, when you do and try to map that user to the imported database, you’ll get a message similar to the one below:

Create failed for user ‘<username>’.

User, group, or role ‘<username>’ already exists in the current database.

Unfortunately, this “existing” user doesn’t really exist.  We need to drop the user from the imported database.  You might try the DROP USER command, but that’d likely greet you with the following error:

Drop failed for user ‘<username>’.

The database principal owns a database role and cannot be dropped.

In many applications, but mostly in the DNN world, that user will likely have owned the ASP.Net application roles (database roles in the database).  All you have to do is change the owner for those roles, and then you can reassign your favorite SQL user account to your development database.

   1: USE [<database_name>];

   2:  

   3: ALTER AUTHORIZATION ON ROLE::[aspnet_Membership_FullAccess] TO [dbo];

   4: ALTER AUTHORIZATION ON ROLE::[aspnet_Membership_BasicAccess] TO [dbo];

   5: ALTER AUTHORIZATION ON ROLE::[aspnet_Membership_ReportingAccess] TO [dbo];

   6: ALTER AUTHORIZATION ON ROLE::[aspnet_Profile_FullAccess] TO [dbo];

   7: ALTER AUTHORIZATION ON ROLE::[aspnet_Profile_BasicAccess] TO [dbo];

   8: ALTER AUTHORIZATION ON ROLE::[aspnet_Profile_ReportingAccess] TO [dbo];

   9: ALTER AUTHORIZATION ON ROLE::[aspnet_Membership_FullAccess] TO [dbo];

  10: ALTER AUTHORIZATION ON ROLE::[aspnet_Roles_FullAccess] TO [dbo];

  11: ALTER AUTHORIZATION ON ROLE::[aspnet_Roles_BasicAccess] TO [dbo];

  12: ALTER AUTHORIZATION ON ROLE::[aspnet_Roles_ReportingAccess] TO [dbo];

  13:  

  14: DROP USER <username>;

Please Note: Make sure you change <database_name> and <username> to their appropriate values in your environment.