Database permissions and least privilege

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Check the current database user’s object permission before planning narrower grants.

Run the example

SELECT HAS_PERMS_BY_NAME(N'dbo.Products', N'OBJECT', N'SELECT') AS CanSelectProducts;

Read it step by step

1. HAS_PERMS_BY_NAME checks the current database user’s effective SELECT permission on dbo.Products without first reading the table.
2. A result of 1 means that user has effective permission; 0 means no effective permission, which does not by itself prove an explicit DENY. NULL can mean invalid input or unavailable information.
3. Least privilege means granting only needed actions on needed objects to a dedicated application role.

Check the result

The displayed 1 belongs to the local verification database user. Another learner’s result may differ; the query does not create grants.

Practice

Check the same database user’s SELECT permission on dbo.Customers without first selecting from that table.

SELECT HAS_PERMS_BY_NAME(N'dbo.Customers', N'OBJECT', N'SELECT') AS CanSelectCustomers;