How to grant only read(Select) permissions to the Group of Users

  • I have a business requirement where Business Users are all available in Group "TradingBusinessUsers" , Now i want to grant select permissions to all tables for the users in the group "TradingBusinessUsers"  and also users are allowed to create table on their own Self-schema and manage their tables.

    Can anyone help me to solve this business case?

  • Define a role. Put the users in that role. Then GRANT SELECT to the role for the schema in question. You can also grant additional functionality to the role. For added protection, you can always DENY permissions to, just to be sure. However, usually, limited GRANT statements work fine. Just work through the role. It's a great way to control behaviors without having to manage individuals. 3-5 roles in the database and then simply put groups or individuals into or out of the roles. Done. Easy.

    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • To add to what Grant said, create the role in your database and then add the group (I assume this is an AD group) to the role with ALTER ROLE ... ADD MEMBER.

    This allows you to control permissions for the group,  but also test by adding other accounts to the role if needed. To allow the role to create tables in their own schema, you will need to grant them the CREATE TABLE permission.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply