Database User Securables Question

Question

Learning sql server over here and I'm trying to figure out how to see the securable permissions I have added via T-SQL , in SSMS

For example, I have a database user called "LoginUser1" in Database1 and I granted "grant create table, create view to LoginUser1".

I'm trying to see this permission in SSMS. I go to the properties for the user in this database but i'm not seeing anything. What am I missing? Thanks!

score 5 · Answer 1 · edited Jul 01 '17 at 00:45

5

Query presented by @S4V1N will do the trick. If you have to see it via GUI using SSMS you can select:

Database → Properties → Permissions → Select the user LoginUser1 from Users or roles: list.

Select Explicit, you will see this.

Select Effective, you will see this.

edited Jul 01 '17 at 00:45

Evan Carroll

63,051
46
242
479

answered Jun 29 '17 at 20:57

SqlWorldWide

13,153
3
28
52

@cspell you are welcome. Do you mind accept it as an answer so other users can be benefited? – SqlWorldWide Jun 30 '17 at 14:29

S4V1N · Answer 2 · 2017-06-29T20:32:09.897

3

You can try this :

SELECT
   ISNULL(OBJECT_NAME(major_id),'') [Objects], USER_NAME(grantee_principal_id) as [UserName], permission_name as [PermissionName]
FROM
    sys.database_permissions p
WHERE grantee_principal_id>0
ORDER BY
    OBJECT_NAME(major_id), USER_NAME(grantee_principal_id), permission_name

edited Jun 29 '17 at 20:32

answered Jun 29 '17 at 20:07

S4V1N

1,064
6
12

1

I would add a where clause to limit the result set for LoginUser1. – SqlWorldWide Jun 29 '17 at 20:27
1

Thanks, I'm all about the sql, I was just trying to see where this is in the GUI – cspell Jun 30 '17 at 14:25

Database User Securables Question

2 Answers2