How to create database roles in SQL Server: step by step

Creating database roles in SQL Server is the cleanest, most scalable way to control access. Instead of granting permissions directly to each user, you group the permissions into a role and add people to that role. When someone changes jobs, you just swap the member, and data governance becomes far easier to manage and audit.

Prerequisites

• A SQL Server instance (2012 or later) or Azure SQL Database.
• SQL Server Management Studio (SSMS) or Azure Data Studio to run the queries.
• CREATE ROLE permission on the database, or membership in the db_securityadmin role.
• A test database where you can experiment safely.

Step 1: Create the database role

A database role is a container of permissions at the database level. Create one with the CREATE ROLE statement and give it a name that describes the business function — for example, people who only need to read sales data:

USE VendasDW;
GO

CREATE ROLE leitura_vendas;
GO

SQL Server already ships with fixed roles (such as db_datareader or db_owner), but creating your own roles gives you fine-grained control over what each group can do. The new role is created but still empty: no permissions and no members. This is intentional — first we create the container, then we fill it with permissions.

Step 2: Grant permissions to the role

Now you define what the role can do with GRANT. Because the goal is governance, follow the principle of least privilege: grant only what is needed. In this example we grant read access over an entire schema:

GRANT SELECT ON SCHEMA::vendas TO leitura_vendas;
GO

Notice that you grant the permission to the role, not to a person — that is where the benefit lies. Every member automatically inherits everything the role has, so you never repeat the GRANT user by user. If you need to block an action explicitly, use DENY, which always takes precedence over GRANT.

Step 3: Add members to the role

To add someone, you need a user in the database. If one does not exist yet, create it from a login and then add it to the role with ALTER ROLE ... ADD MEMBER:

-- create a login and a user (use a strong password)
CREATE LOGIN ana WITH PASSWORD = 'Muda_esta_Password_123!';
CREATE USER ana FOR LOGIN ana;
GO

-- add the user to the role
ALTER ROLE leitura_vendas ADD MEMBER ana;
GO

From now on, Ana can read the vendas schema without any direct permission of her own. You can add as many members as you need, always with the same statement.

Best practice: grant permissions to roles, never directly to users. That way each person's access is simply the sum of the roles they belong to — easy to read and easy to revoke.

Step 4: Remove members and manage the role

When someone moves to another team, you remove the member without touching the role's permissions:

ALTER ROLE leitura_vendas DROP MEMBER ana;
GO

If you need to rename the role, use ALTER ROLE leitura_vendas WITH NAME = leitura_faturacao;. To delete it, first make sure it has no members and run DROP ROLE leitura_vendas;.

Check the result

To confirm who belongs to each role, query the system views sys.database_role_members and sys.database_principals:

SELECT r.name AS role_name, m.name AS membro
FROM sys.database_role_members AS drm
JOIN sys.database_principals AS r ON drm.role_principal_id = r.principal_id
JOIN sys.database_principals AS m ON drm.member_principal_id = m.principal_id
ORDER BY r.name, m.name;

You should see the row linking leitura_vendas to Ana, while she is still a member. To test the permissions in practice, you can impersonate the user's session and then return to your own context with REVERT:

EXECUTE AS USER = 'ana';
SELECT TOP (5) * FROM vendas.Faturas;   -- should return data
REVERT;

Conclusion

With database roles, access management stops being an endless list of individual permissions and becomes organized by function: more secure, easier to audit and aligned with data governance best practices. The natural next step is to map your organization's real profiles — read, write, administration — and create a role for each one. Which access functions already exist in your database that you could turn into a role today?