1

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.

2

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  
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.