SQL Server – Granting Permissions to a Database User

sql serverusers

I need to grant select and update permissions to a DB user (TestUser) on TestDB database as per below.

enter image description here

When I press ok and re-check the permissions, they are not visible as can be shown below:

enter image description here

From my testing, only permissions at object level (e.g. columns, etc) are displayed here.

Can select permission on the database be visible from somewhere apart by executing T-SQL code? Is this a bug or by design? The same applies to user defined DB role permissions and it is very misleading.

Best Answer

Reason you are not seeing the permission when you open the window by default it is scoped to Securable scope: Schema' and notdatabase`.

More on secureables.

Can select permission on the database be visible from somewhere apart by executing T-SQL code?

Yes it is possible. From your 2nd screen:

  • Click Search...
  • Select radio button All objects of the types....
  • Click Ok
  • Select Databases

You should see TestDB in the list at top half of the window. In the bottom half you can see Explicit and Effective permission.

In case you want to use tsql:

EXECUTE AS USER = 'testuser';  
USE [DbaDatabase];  
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');  
REVERT;
GO
Related Question