3

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!

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.

enter image description here

Select Effective, you will see this.

enter image description here

  • @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
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
  • 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

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.