@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