How can you enforce restrictions on user access to a database table, preventing indirect access through views, procedures, or GUI operations, without relying on SQL scripts?

Shabnam A. Shaikh 0 Reputation points
2024-04-17T09:47:19.2133333+00:00

The question is about securing access to a database table in such a way that restrictions apply even when users access the data indirectly through views, procedures, or GUI operations, all without using SQL scripts.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,739 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Greg Low 1,495 Reputation points Microsoft Regional Director
    2024-04-18T01:04:56.6+00:00

    You assign the permissions directly to the tables.

    Then, for example, to avoid a stored procedure owned by the table owner allowing access indirectly, you have two options:

    • The first (most basic) is to not give people the permission to run the procedure if they shouldn't have it
    • OR add the clause WITH EXECUTE AS CALLER to the header of the stored procedure. That causes all the code in the procedure to be checked with the caller's credentials, not the proc/table owner's permissions.

    It does sound like you need to have a rethink about how your overall security works though. Ideally, no-one apart from the owner would have access to the tables, and the only way they'd get to them would be via views and/or stored procedures that you grant access to. And that of course assumes that none of the people who shouldn't be database owners or sysadmins are in those roles.

    0 comments
  2. LucyChen-MSFT 1,045 Reputation points Microsoft Vendor
    2024-04-18T06:20:39.1066667+00:00

    Hi @Shabnam A. Shaikh,

    Thanks for your information.

    I agree with Greg Low. If you want to enforce restrictions on user access to a database table:

    You assign the permissions directly to the tables.

    I followed the steps in this article, and I change the permission of the table:

    User's image Then you can now view-only permissions on the tables you have specified. You cannot access any data.

    User's image

    User's image

    In addition, here is a thread like yours, I think maybe the contained database can resolve your issue. Please check this link if you need it.

    https://learn.microsoft.com/en-us/answers/questions/1654276/hide-sql-dbs-users-do-not-have-access-to

    Best regards,

    Lucy Chen

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    https://docs.microsoft.com/en-us/answers/support/email-notifications