Some links on this page are affiliate links: if you buy through them we may earn a commission, at no extra cost to you.
To give someone access in a SQL Server database, grant the specific permission they need—ideally to a database role, then add the user to that role. For example, this grants read access to objects in a dedicated Reporting schema:
USE SalesDb;
GO
CREATE ROLE ReportingRole;
GRANT SELECT ON SCHEMA::Reporting TO ReportingRole;
ALTER ROLE ReportingRole ADD MEMBER ReportingUser;
GOReportingUser must already exist as a user in SalesDb. Granting a login access to the server is not the same as authorizing it to read or change data in a database.
Understand logins, users, roles, and permissions
A login authenticates a person or service at the server level. A database user is the principal SQL Server authorizes inside a particular database. A database role groups users who need similar access, and permissions are assigned to users or roles on protected resources called securables.
Free tools Windows power users keep installed
One-click scans. No signup required.
Login or contained identity
↓
Database user
↓
Database role membership
↓
Permission on database, schema, object, or columnThis is the usual model on boxed SQL Server. Azure SQL Database also supports contained database users, including Microsoft Entra identities, and its server-level access model differs from a SQL Server instance. Check the applicable [Microsoft guidance on logins and database users](https://learn.microsoft.com/en-us/azure/azure-sql/database/logins-create-manage?view=azuresql) before applying server-login examples to Azure.
#1 Best Overall
Permissions can apply at server, database, schema, object, or column scope. A broader scope affects more resources. Choose the narrowest scope that meets the need; see Microsoft’s overview of SQL Server securables and Database Engine permissions.
Choose the permission and scope
| Need | Typical permission | Example scope |
|---|---|---|
| Read rows | SELECT |
A view, table, or reporting schema |
| Add rows | INSERT |
A particular table or application schema |
| Change rows | UPDATE |
A particular table; consider column scope if suitable |
| Delete rows | DELETE |
A particular table |
| Run a stored procedure | EXECUTE |
A procedure or API schema |
| See object definitions | VIEW DEFINITION |
An object or database |
| Create tables | CREATE TABLE |
Database |
The same permission can have very different reach depending on scope. SELECT on one object is narrower than SELECT on a schema, which is narrower than a database-wide reader role. Avoid granting permissions simply because a role name sounds convenient.
Create or identify the database user
First connect to the target database and confirm whether the user already exists. On SQL Server, an existing login can be mapped to a database user like this:
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteUSE SalesDb;
GO
CREATE USER AppUser FOR LOGIN AppLogin;
GOFor a Windows account or group, use its exact domain-qualified name:
CREATE USER [CONTOSOSales Analysts]
FOR LOGIN [CONTOSOSales Analysts];
GOA contained SQL user is created inside the database rather than mapped to a server login. Syntax and supported identity types vary by SQL Server and Azure service; for example, Azure SQL Database supports database-contained users. Do not copy a contained-user command into an environment without confirming it supports that authentication method. A user-creation example for a contained SQL user is:
CREATE USER ReportingUser
WITH PASSWORD = 'Use-A-Strong-Secret-Here';Use a securely managed secret rather than a literal production password in a script or source repository. If you need to create a SQL Server login, that is a separate server-level operation; it does not itself grant access to database objects.
Recommended approach: grant to a custom role
Roles are easier to review and maintain than a collection of individual grants. This example gives members read permission on the Reporting schema:
USE SalesDb;
GO
CREATE ROLE ReportingRole;
GO
GRANT SELECT ON SCHEMA::Reporting TO ReportingRole;
GO
ALTER ROLE ReportingRole ADD MEMBER ReportingUser;
GOThe role must exist before users can be added to it, and the user and role must be in the target database. Use ALTER ROLE ... ADD MEMBER for new scripts rather than the older sp_addrolemember procedure. A schema grant applies to the schema’s objects, so review that schema’s contents—including objects added later—to ensure they share the same access boundary. See Microsoft’s schema permission guidance.
To grant access to only one table, use an object-level permission instead:
GRANT SELECT
ON OBJECT::dbo.Customers
TO ReportingRole;Common T-SQL grants
Grant read or write access to one object
GRANT SELECT
ON OBJECT::dbo.Customers
TO ReportingRole;
GRANT INSERT, UPDATE
ON OBJECT::dbo.CustomerNotes
TO CustomerServiceRole;Use the actual schema-qualified object name. A table named Customers is not necessarily dbo.Customers.
Grant permission on a view or procedure
GRANT SELECT
ON OBJECT::dbo.CustomerSummary
TO ReportingRole;
GRANT EXECUTE
ON OBJECT::dbo.usp_GetCustomer
TO AppRole;For an application, granting execution on a deliberately designed set of procedures can avoid direct table permissions. It is not automatically safe: review what the procedures return, any dynamic SQL they execute, their ownership and execution context, and their input handling.
Recommended Free Tools
Grant permissions on a schema
GRANT SELECT
ON SCHEMA::Reporting
TO ReportingRole;
GRANT EXECUTE
ON SCHEMA::Api
TO AppRole;Schema scope is useful when its objects form a consistent security boundary. It can also expose future objects placed in that schema, so review the schema design and deployment practices.
Grant at database scope
Database-level permissions are broader and should be chosen deliberately. For example, a developer role may need to create tables, or a role may need to view definitions throughout the database:
GRANT CREATE TABLE TO DeveloperRole;
GRANT VIEW DEFINITION
ON DATABASE::SalesDb
TO DeveloperRole;Do not use GRANT ALL as shorthand for full access. Microsoft documents ALL as deprecated, and it does not mean every possible permission. Use named permissions and the appropriate securable scope. Refer to the current GRANT documentation for syntax and supported permissions.
When fixed database roles are appropriate
Fixed roles are convenient for simple cases, but can be much broader than a production task requires:
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minutedb_datareadercan read all user tables and views in the database.db_datawritercan insert, update, and delete data across user tables.db_ownerhas full control of the database.db_ddladminis intended for database definition tasks, not routine data access.
For example, membership is added with:
ALTER ROLE db_datareader ADD MEMBER ReportingUser;This can be reasonable when database-wide access is genuinely intended. It is not equivalent to access to a curated reporting area. For narrower production access, create a custom role and grant only the needed permissions. Microsoft notes that fixed roles may suit simple setups, while more granular permissions are preferable in many scenarios; see authorization guidance.
Grant column-level access carefully
For supported permissions, SQL Server can limit a grant to selected columns:
GRANT SELECT (CustomerId, DisplayName, Region)
ON OBJECT::dbo.Customers
TO LimitedReportingRole;Column grants are useful in specific cases, but should not be treated as a universal data-isolation mechanism. SQL Server documents a backward-compatibility exception in which a table-level DENY does not override a column-level GRANT. Test the exact effective permissions and query paths. See object and column permission details.
Use SSMS to grant permissions
In SQL Server Management Studio (SSMS), the path varies by securable and version, but for a table, view, or procedure the general steps are:
- Connect to the instance and expand Databases in Object Explorer.
- Expand the target database and locate the object.
- Right-click the object and select Properties.
- Open Permissions, then use Search to add the database user or role.
- Select the principal and choose the appropriate explicit permission state—Grant, Grant with Grant, or Deny—then apply the change.
For a stored procedure, the documented path is under Programmability → Stored Procedures, then right-click the procedure, choose Properties → Permissions. To manage role membership, expand the database’s Security → Roles → Database Roles, open the role’s properties, and use its Members page. Exact labels can vary. See Microsoft’s SSMS permission steps.
The SSMS grid shows explicit permissions, not necessarily every path by which access is inherited. A user may also receive access through roles, Windows groups, broader grants, ownership, or execution context. Use queries and test under the intended identity as well.
Verify the permission and identity
Check the execution context first if an application or user reports unexpected access:
SELECT
SUSER_SNAME() AS LoginName,
ORIGINAL_LOGIN() AS OriginalLogin,
USER_NAME() AS DatabaseUser,
DB_NAME() AS DatabaseName;List database users and their authentication type:
SELECT
name,
type_desc,
authentication_type_desc,
default_schema_name
FROM sys.database_principals
WHERE type NOT IN ('R', 'X')
ORDER BY name;Review role membership:
SELECT
role_name = roles.name,
member_name = members.name
FROM sys.database_role_members AS drm
JOIN sys.database_principals AS roles
ON roles.principal_id = drm.role_principal_id
JOIN sys.database_principals AS members
ON members.principal_id = drm.member_principal_id
ORDER BY roles.name, members.name;Review explicit permissions recorded in the database:
SELECT
grantee.name AS grantee_name,
grantee.type_desc AS grantee_type,
dp.state_desc,
dp.permission_name,
dp.class_desc,
major_name =
CASE dp.class
WHEN 0 THEN DB_NAME()
WHEN 1 THEN OBJECT_SCHEMA_NAME(dp.major_id)
+ N'.'
+ OBJECT_NAME(dp.major_id)
WHEN 3 THEN SCHEMA_NAME(dp.major_id)
END
FROM sys.database_permissions AS dp
JOIN sys.database_principals AS grantee
ON grantee.principal_id = dp.grantee_principal_id
ORDER BY grantee.name, dp.class_desc, dp.permission_name;state_desc can show GRANT, GRANT_WITH_GRANT_OPTION, or DENY. A revoked permission is generally represented by the absence of an explicit permission row, not a positive REVOKE entry. These catalog results show explicit configuration; they are not, by themselves, a complete explanation of effective access.
Rank #4
Check a named permission in the current execution context with HAS_PERMS_BY_NAME:
SELECT HAS_PERMS_BY_NAME(
'dbo.Customers', 'OBJECT', 'SELECT'
) AS CanSelectCustomers;You can also inspect permissions available to the current user:
SELECT *
FROM sys.fn_my_permissions(NULL, 'DATABASE');
SELECT *
FROM sys.fn_my_permissions('dbo.Customers', 'OBJECT');To test as a database user, use impersonation where you have the required authority:
EXECUTE AS USER = 'ReportingUser';
SELECT
USER_NAME() AS DatabaseUser,
DB_NAME() AS DatabaseName,
HAS_PERMS_BY_NAME(
'Reporting.Customers', 'OBJECT', 'SELECT'
) AS CanSelectCustomers;
REVERT;Run the test in the target database. HAS_PERMS_BY_NAME checks a named permission and scope; it does not replace checking role membership, broader permissions, or the application’s actual connection identity.
Change or remove access
Remove a user from a custom role when they should no longer inherit its permissions:
ALTER ROLE ReportingRole DROP MEMBER ReportingUser;Remove an explicit grant at its scope with REVOKE:
REVOKE SELECT
ON SCHEMA::Reporting
FROM ReportingRole;REVOKE removes an explicit grant or deny at the specified scope; it does not cancel access the user still receives through another role, a Windows group, a broader grant, or another valid permission path.
DENY explicitly blocks a permission and generally takes precedence over a grant at the same or a lower scope. However, SQL Server has documented exceptions, including the column-level behavior above. Prefer a clean least-privilege role design over using DENY to patch an overly broad grant. See Microsoft’s GRANT, DENY, and REVOKE reference.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
WITH GRANT OPTION lets the recipient grant the permission to other principals. Use it only when the recipient is meant to administer access; it expands the permission-management boundary and complicates review.
Best Value
Troubleshoot common permission failures
“The user exists, but cannot connect”
- Confirm the login, contained identity, or other authentication method is available and enabled.
- Confirm you are connecting to the intended server and database, and that the user is created in the right database.
- Check for a database-level
DENY CONNECTor an unavailable database. - Confirm the platform and identity syntax match: Azure SQL Database, Managed Instance, and boxed SQL Server do not have identical server-level models.
On SQL Server 2022 and later, the ##MS_DatabaseConnector## server role is relevant to connection access across databases; it is distinct from permission to read or modify database objects. See Microsoft’s server-level role documentation.
“SELECT permission was denied”
- Check
DB_NAME()and the fully qualified object name. - Confirm the grant targets the database user or a role the user actually belongs to—not just a server login.
- Check explicit permissions and role membership, including for Windows groups.
- Look for a
DENYat a relevant scope. - Confirm the application is using the identity you expect.
- If access is through a view, procedure, synonym, or cross-database reference, inspect that full path and its execution context.
Cross-database access
A grant in one database does not automatically create a user or grant permission in another. The principal generally needs an appropriate identity and permissions in each database it accesses. Verify the security design for the particular platform rather than assuming that a server login alone authorizes database access.
Windows groups
Granting to a managed Windows group can simplify onboarding and offboarding. You can create a database user for the group, add it to a database role, and grant permissions to the role. If a group membership change appears not to take effect, verify the identity token and connection context with the identity administrator; cached authentication context or nested group membership may affect what the session presents.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Orphaned users after a restore or migration
A database user mapped to an instance login by security identifier (SID) can lose that mapping after restore or migration if the corresponding login differs. This diagnostic finds instance-authenticated database users with no matching server principal SID:
SELECT
dp.name AS DatabaseUser,
dp.type_desc,
sp.name AS LoginName
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp
ON dp.sid = sp.sid
WHERE dp.authentication_type_desc = 'INSTANCE'
AND dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys');Interpret results before making changes: contained users do not use the same login mapping, and the correct repair depends on whether the intended login exists and on the SQL Server or Azure service. Correct the mapping to the intended login rather than creating duplicate users blindly.
Ownership chaining and indirect access
Ownership chaining can let a user execute a procedure or access an object through another object without a direct grant on every underlying object. This can support a controlled application interface, but changes to ownership, schema permissions, or dynamic SQL can alter the security boundary. In particular, do not grant ALTER on a schema merely because someone needs to read its objects; schema alteration can have broader consequences. Review the schema permission implications.
Keep database access least-privilege
- Start from the action needed, then grant the narrowest permission and scope that supports it.
- Prefer custom roles for repeatable access, and grant to managed groups where that fits your identity model.
- Use object grants for a small set of objects or a dedicated schema when objects share a security boundary.
- Reserve
db_owner,db_securityadmin, and permission-administration rights for people who genuinely need them. - Avoid broad roles,
GRANT ALL, and unnecessaryWITH GRANT OPTION. - Test with the application’s actual identity and database, not only an administrator session.
- Review permissions after deployments, restores, and changes to schema contents or role membership.
For a concise reference to permission hierarchy and syntax, use the current Microsoft documentation for permission hierarchy and GRANT.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
