azure-sql-read-only-user.sql

/*
  Filename: azure-sql-read-only-user.sql

  Purpose:  Create a read-only login user on a target database to enable the access to the data, but disable any insert, update, delete operations.

  Author:
  Oscar Garcia @ozkary

  Reference:  

*/
-- select master database to create the login profile
USE master;
GO

-- creates the login account
CREATE LOGIN [rptLogin] WITH password='your-pw-here';
-- DROP LOGIN rptLogin;

-- enable the access to login to the database
CREATE USER [rptUser] FROM LOGIN [rptLogin];
-- DROP USER [rptUser];

-- move to the target database context
-- if this is not supported, open another connection directly on the target database and run this script
-- using SSMS select the target database from the database dropdown
USE myDatabase;
GO

-- create the user on the target database
CREATE USER [rptUser] FROM LOGIN [rptLogin] WITH DEFAULT_SCHEMA=[dbo];

-- add the data reader role to the user
EXEC sys.sp_addrolemember @rolename = N'db_datareader', @membername = N'rptUser'