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