CODEWITHSUNDEEP
sql-servert-sqlazure-sql-database
nealsu
nealsu

Reputation: 438

Can't add new user to Azure SQL Database

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.

Upvotes: 3

Views: 3239

Answers (1)

nealsu
nealsu

Reputation: 438

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.

Upvotes: 6

Related Questions