CODEWITHSUNDEEP
sqlazure-sql-database
dlimes
dlimes

Reputation: 105

Set permissions by role in Azure SQL Database

I'm attempting to create 4 different roles in Azure SQL database. The 4 role names are READ_ONLY_ACCESS, READ_UPDATE_ACCESS, READ_WRITE_UPDATE_ACCESS, FULL_ACCESS.

-READ_ACCESS - Grant Select
-READ_UPDATE_ACCESS - Grant Select, Update
-READ_WRITE_UPDATE_ACCESS - Grant select, Update, Insert
-FULL_ACCESS - Grant Select, Update, Insert, Delete

My goal is to create these 4 roles with these permissions, and then be able to create a user assigned to one of these roles. Example below of what I want to do after creating these roles:

CREATE USER username WITH PASSWORD = 'password';

ALTER ROLE READ_ACCESS ADD MEMBER username;

I've found a few sites that help with this, but I'm unclear as to whether or not I need to declare whether to deny access to certain permissions as well as declaring the granted access.

Upvotes: 2

Views: 5709

Answers (1)

Alberto Morillo
Alberto Morillo

Reputation: 15702

First create roles.

create role ApplicationUsers_ReadAccss;
grant select on schema::dbo to ApplicationUsers_ReadAccss;

create role ApplicationUsers_ReadUpdateAccss;
grant select, update on schema::dbo to ApplicationUsers_ReadUpdateAccss;

create role ApplicationUsers_ReadWriteUpdateAccss;
grant select, insert, update on schema::dbo to ApplicationUsers_ReadWriteUpdateAccss;

create role ApplicationUsers_FullAccess;
grant select, insert, update, delete, execute on schema::dbo to ApplicationUsers_FullAccess;

After that create logins and add them to one of the roles.

--create a server-level Login

create login AppUser with Password ='asdfAds01980(*)(*)(#&$)#@';

--add a user mapped to that login in each database 

create user AppUser for login AppUser;
alter role ApplicationUsers_FullAccess add member AppUser;

Upvotes: 4

Related Questions