Cannot add user to db_datareader role in database.

Brian 20 Reputation points
2024-01-25T17:47:01.2266667+00:00

We're using Azure Sql Server. My sql user has been added to the ##MS_DatabaseManager## and ##MS_LoginManager## role in master. I created a new login (in master). I created a new user for the login (in a database). I try to add that new user to the db_datareader role in that database, but cannot.

-- create login in master
CREATE LOGIN databricks_login
WITH PASSWORD='something secret';

-- create user in database
CREATE USER databricks_user 
FOR LOGIN databricks_login
WITH DEFAULT_SCHEMA = dbo; 
  
-- add user to role(s) in database 
ALTER ROLE db_datareader ADD MEMBER databricks_user;

ERROR [11:1]:(SQLSTATE: S0001, SQLCODE: 15151): Cannot alter the role 'db_datareader', because it does not exist or you do not have permission.
ALTER ROLE db_datareader ADD MEMBER databricks_user; 

-- check that the role and user exist in database
SELECT name, type FROM sys.database_principals WHERE name in ('db_datareader', 'databricks_user');

databricks_user	S
db_datareader	R

-- check my database permissions
SELECT permission_name FROM sys.fn_my_permissions(null, 'DATABASE') WHERE permission_name LIKE 'ALTER%ROLE';

ALTER ANY ROLE
ALTER ANY APPLICATION ROLE
Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,437 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 116.5K Reputation points MVP
    2024-01-25T22:43:01.88+00:00

    For one reason or another, you cannot grant permissions on the the fixed roles. And the fixed roles have different level of privileges. db_datareader may uncontroversial, but db_owner is not. Several of the other roles are also quite powerful. Thus, requiring membership in db_owner makes sense to me. But you can do this:

    CREATE ROLE MyDataReader
    ALTER ROLE db_datareader ADD MEMBER MyDataReader
    

    And then you can work with MyDataReader.


2 additional answers

Sort by: Most helpful
  1. Oury Ba-MSFT 20,186 Reputation points Microsoft Employee
    2024-01-25T20:13:10.9466667+00:00

    @Brian Thank you for reaching out. I tried to reproduce the issue from my end, and I have successfully run the above code. You are trying to add a new user to the db_datareader role in a database, but you are getting an error message. You can check if the role exists in the database, create it if it does not exist. If the role exists, you can try to add the user to the role again. If the role does not exist, you can create it using the following query: SQL

    CREATE ROLE db_datareader;
    

    If you still encounter issues, you can try to grant the user the SELECT permission on the tables directly using the following query: SQL

    GRANT SELECT ON <table_name> TO databricks_user;
    

    https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver16 Alternatively, instead of creating a user in the traditional way you can create the new user as a contained user. You just have to connect to the database and use the following syntax: Transact-SQLCopy

    CREATE USER [databaseUser] WITH PASSWORD = 'xxxxxxxxxxx';
    ALTER ROLE [db_datareader] ADD MEMBER [databaseUser]
    
    

    Regards, Oury


  2. Brian 20 Reputation points
    2024-01-25T20:44:46.1+00:00

    I reproduced this all again from scratch and the only thing that worked was making adding my user to the db_owner role in the database. That cannot possibly be right. Except this article does specifically say "Adding members to fixed database roles requires membership in the db_owner fixed database role.". Ouch!


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.