Sql-server – Alter role shows role does not exists

azure-sql-databasesql server

Running alter role db_ddladmin add member sqlloginname

gives the error message:

Cannot alter the role 'db_ddladmin' because it does not exist or you
do not have permission.

This statement I am executing in master db from Server admin login in azure sql db. I am not able to find any reason of that.

Best Answer

You do not have permission to add users to the db_ddladmin role in the master database.

Since

The Server admin and Azure AD admin accounts have the following characteristics:

Do not enter the master database as the dbo user, and have limited permissions in master.

Are not members of the standard SQL Server sysadmin fixed server role, which is not available in SQL database.

Can create, alter, and drop databases, logins, users in master, and server-level IP firewall rules.

Source

When querying the sys.database_role_members system view in the master database, you will also see that only dbo is part of the db_owner role. For most or all other roles, no members show up.

DatabaseRoleName         DatabaseUserName
db_accessadmin           No members
db_backupoperator        No members
db_datareader            No members
db_datawriter            No members
db_ddladmin              No members
db_denydatareader        No members
db_denydatawriter        No members
db_owner                 dbo
db_securityadmin         No members
dbmanager                No members
loginmanager             No members
public                   No members

This means that specific permissions are granted to the user connecting to the master database, adding users to the db_ddladmin role in master is not one of them.

As documented, you can add users to the dbmanager & loginmanager db roles in master.

alter role [dbmanager] add member sqlloginname;
alter role [loginmanager] add member sqlloginname;

You can add users to the db_ddladmin role in user databases when connecting with the server admin since you will have dbo permissions there.

Remember that these are database roles, and that you will have to add the user per database.

Related Question