What is the use of ROLES in Azure Syanpse if I cannot add any USER to it?

Question

I am creating users, in particular, login_id in Azure synapse using Admin login id and password.

As of now, I have to give permission individually to every login using the code below. (brown_batman is a user created in login_id brown_batman for database adventure2016)

grant Select,Alter,Update,Execute,Insert on database::[adventure2016] to brown_batman;

I am creating a role and giving permissions to that role in adventure2016 DB using the code below:

CREATE ROLE Perm;  
grant Select,Alter,Update,Execute,Insert on database::[adventure2016] to Perm

Now, if I try to add users to the role using code ALTER ROLE Perm ADD Member brown_batman; then I get the error

Parse error at line: 1, column: 17: Incorrect syntax near 'ADD'.

I am really trying to understand the use of roles when I cannot add users to a specific role.

user_1690193 · Answer

See the doco here;

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/sql-authentication?tabs=serverless

In Azure SQL Database or synapse serverless, use the ALTER ROLE statement.

ALTER ROLE db_owner ADD MEMBER Mary;

I assume you aren't using serverless, so you need to use this syntax:

In dedicated SQL pool use EXEC sp_addrolemember

EXEC sp_addrolemember 'db_owner', 'Mary';

What is the use of ROLES in Azure Syanpse if I cannot add any USER to it?

1 Answers