Can't add new user to Azure SQL Database

Question

Trying to add a new SQL Server Authentication user to an Azure SQL Database instance. The script below is being used. I am logged in as the server admin.

But the script fails at the member role assignment. The login and user are created but I cannot login to the database server.

Script:

use [master]

CREATE LOGIN test WITH password='somethingsecure';
GO

CREATE USER test FROM LOGIN test;
GO

EXEC sp_addrolemember 'db_datareader', 'test';

EXEC sp_addrolemember 'db_datawriter', 'test';

However the above script fails with the following error:

Msg 15151, Level 16, State 1, Line 6 Cannot alter the role 'db_datareader', because it does not exist or you do not have permission.

Msg 15151, Level 16, State 1, Line 6 Cannot alter the role 'db_datawriter', because it does not exist or you do not have permission.

What edition is this? use is not supported in Azure SQL. Is this managed instance? — Nick.McDermaid
@Nick.McDermaid - It is not supported via SSMS, for that you will have to manually switch between the databases, when I run this via my DBUP scripts it works fine. — nealsu
This docs.microsoft.com/en-us/sql/t-sql/language-elements/… says In Azure SQL Database, the database parameter can only refer to the current database. If a database other than the current database is provided, the USE statement does not switch between databases, and error code 40508 is returned Maybe you're in master or maybe this isn't Azure SQL. I only mention it because database contained users are preferred in Azure SQL over server level users, so you shouldn't be using CREATE LOGIN at all. — Nick.McDermaid
@Nick.McDermaid you are probably right here, I think this worked for me across two runs where the current database changed, testing it again it wasn't allowed. Would you like to add an answer that would work to CREATE USER with password rather than using a login? I'd be happy to mark that as answer. — nealsu

nealsu nealsu · Accepted Answer · 2021-07-13T05:24:58

The issue is trying to add the User and Roles to the master database.

All that is needed is to create the Login against master and then create the User and assign Roles against the actual database in the Azure SQL Database server.

See modified script:

use [master]

CREATE LOGIN test WITH password='somethingsecure';
GO

use [your-database]

CREATE USER test FROM LOGIN test;
GO

EXEC sp_addrolemember 'db_datareader', 'test';

EXEC sp_addrolemember 'db_datawriter', 'test';

Note: If you want to create a User with access to the master database, this answer will not help.

Can't add new user to Azure SQL Database

1 Answers