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)
FROM sys.database_role_members rm1 (NOLOCK)
FROM sys.database_role_members rm (NOLOCK)
INNER JOIN RoleMembers AS d
ON rm.member_principal_id = d.role_principal_id
select distinct as database_role, 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