Share via

SQL Users administration, permissions

Elias, Filip 20 Reputation points
2023-04-16T10:07:21.9566667+00:00

Hi. I would like to set User permission to achieve this goal: Deny see View definition (cannot see in Design, Alter view etc.) but see view in "Views list" in MSSMS. (Critical views is created by their own schema.) It is possible?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
Accepted answer
  1. Erland Sommarskog 107.1K Reputation points
    2023-04-16T16:22:54.4466667+00:00

    Your post is not clear, but keep in mind that by default users have no permissions at all, so there is normally not able no need for DENY.

    If you only want the users to be able to access views in a certain schema, and that schema only has these views, you can grant SELECT permission on that schema. If you only want them to be able to SELECT on the views, but not see the view DEFINITION, you do need to DENY them VIEW DEFINITION on the schema. But then will not be able to see them in the views list either.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Seeya Xi-MSFT 16,461 Reputation points
    2023-04-17T02:26:40.78+00:00

    Hi @Elias, Filip,

    Agree with Erland. Please refer to this blog:

    https://www.sqlshack.com/an-overview-of-a-view-definition-permission-in-sql-server/

    Hope this would help you get a better understanding.

    Best regards,

    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it.

    0 comments