Add member failed for DatabaseRole

Question

TITLE: Microsoft SQL Server Management Studio

---

Add member failed for DatabaseRole 'db_accessadmin'. (Microsoft.SqlServer.Smo)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=17.100.30.0&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Add+member+DatabaseRole&LinkId=20476

---

ADDITIONAL INFORMATION:

An exception occurred while Add member failed for DatabaseRole or batch. (Microsoft.SqlServer.ConnectionInfo)

---

Cannot use the special principal 'dbo'. (Microsoft SQL Server, Error: 15405)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-15405-database-engine-error

---

BUTTONS:

OK

---

Answer 1

Hi @Sayed Ahmad Alrefai ,

 This error occurs when particular login in question is associated to ‘dbo’ user in the database. In other words, this particular login is the ‘db_owner’ of the database which is causing the issue. This error occurs while we try to give any database role permissions to the login associated with ‘dbo’ user.

When you create a database, dbo user will created as database user automatically with do_owner role by default. This means dbo user has all permission to this database.

 For example:

CREATE DATABASE TEST
GO
USE [master]
GO
CREATE LOGIN [TestUser] WITH PASSWORD=N'@eE$9@', DEFAULT_DATABASE=[master]                
GO
ALTER AUTHORIZATION ON DATABASE::[Test] TO [TestUser]
GO

To resolve this issue , please change the database owner to ‘sa’, then create a user associated to the login in the Database and then add the login to database roles with the new created user.

 

USE [Test]
GO
-- Change the database owner to 'sa' account
ALTER AUTHORIZATION ON DATABASE::[Test] TO [sa]
GO
USE [Test]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
USE [Test]
GO
ALTER ROLE [db_accessadmin] ADD MEMBER [TestUser]
GO

Refer to this blog to get detail.

 **********************************************************************************

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Answer 2

It seems that you did

ALTER ROLE db_accessadmin ADD MEMBER dbo

As the error message says, you cannot do this for the database user dbo. Nor is there any reason to, since dbo is almighty in the database and already has all powers that come with db_accessadmin.