search

Create Microsoft SQL Azure Database Read-Only User


To create a login (server wide account) which uses a simple username + password combination for SQL authentication:

CREATE LOGIN ReadOnlyUserName WITH PASSWORD = 'strong_password';

NOTE: This allows the user to connect using SQL Management Studio. It is not required if the user only needs to connect to a specific database.

 

To create a user to be used only with a particular application/database:

CREATE USER ReadOnlyUserName WITH PASSWORD = 'strong_password';

NOTE: The user can only connect to the specific database. They cannot connect using SQL Management Studio (unless they have a login too).

Give the new user “select” permissions to a specific table:

GRANT SELECT ON dbo.Books to ReadOnlyUserName;

Give the new user “read-only” permissions to a database:

ALTER ROLE db_datareader ADD MEMBER ReadOnlyUserName;

To give the new user “write” permissions to a database:

ALTER ROLE db_datawriter ADD MEMBER ReadOnlyUserName;

To remove a user or login:

DROP Login ReadOnlyUserName;

DROP USER ReadOnlyUserName;

 

List roles & role members:

WITH RoleMembers (member_principal_id, role_principal_id)
AS
(
SELECT
rm1.member_principal_id,
rm1.role_principal_id
FROM sys.database_role_members rm1 (NOLOCK)
UNION ALL
SELECT
d.member_principal_id,
rm.role_principal_id
FROM sys.database_role_members rm (NOLOCK)
INNER JOIN RoleMembers AS d
ON rm.member_principal_id = d.role_principal_id
)
select distinct rp.name as database_role, mp.name as database_userl
from RoleMembers drm
join sys.database_principals rp on (drm.role_principal_id = rp.principal_id)
join sys.database_principals mp on (drm.member_principal_id = mp.principal_id)
order by rp.name

 

close
search

Hi, guest!

settings

menu