Example of provisioning a Windows Authenticated Domain user in SQL Server. Including restricting the database user to the "db_datareader" role.
-- ----------------------------------------------------------------------------
-- Usage:
-- Replace "DOMAIN\username" with the Windows user name to be provisioned,
-- and "DATABASE_NAME" with the target database name (respectively).
-- ----------------------------------------------------------------------------
--
-- create sql login
create login [DOMAIN\username] from windows with default_database = DATABASE_NAME;
go
use master;
go
grant connect sql to [DOMAIN\username];
go
--
-- create database user
use [DATABASE_NAME];
go
create USER [DOMAIN\username] for login [DOMAIN\username];
go
use [DATABASE_NAME];
go
alter USER [DOMAIN\username] with default_schema = dbo;
go
--
-- database permissions (db_datareader only)
use [DATABASE_NAME];
go
alter authorization on schema ::db_datareader to [DOMAIN\username];
go