How do I create “groups of users” and set permissions in SQL Server?

All we need is an easy explanation of the problem, so here it is.

I am new to SQL Server, and I need to do something like the following:

  • Create a group of users that will allow them to only SELECT from Table 1, but be able to SELECT, UPDATE, DELETE and INSERT INTO Tables 2 and 3;
  • Create another group of users that will allow them to SELECT, UPDATE, DELETE and INSERT INTO all three of the aforementioned tables

All three of the tables are in the same database.

May someone please provide some sample code so that I can accomplish something like that? Thanks!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

If you haven’t already done so, you need to first create a login for each user and add each user to the database. Logins allow users to connect to the SQL Server instance and database users allow users to connect to the database. Once these are created, create a database role with the users as members to grant object permissions to the role.

The annotated example script below uses SQL logins but you can instead use Windows user or group accounts depending on your environment.

--create SQL logins to authenticate users and authorize them to connect to the SQL Server instance
CREATE LOGIN UserA WITH PASSWORD='[email protected]';
CREATE LOGIN UserB WITH PASSWORD='[email protected]';
CREATE LOGIN UserC WITH PASSWORD='[email protected]';
CREATE LOGIN UserD WITH PASSWORD='[email protected]';

--create database users (mapped to login of same name) to authorize users to connect to this database
USE YourDatabase;

--add database users
CREATE USER UserA;
CREATE USER UserB;
CREATE USER UserC;
CREATE USER UserD;

--Create a group of users that will allow them to only SELECT from Table 1, but be able to SELECT, UPDATE, DELETE and INSERT INTO Tables 2 and 3;
USE YourDatabase;

--create role with members
CREATE ROLE Group1Role;
ALTER ROLE Group1Role
    ADD MEMBER UserA;
ALTER ROLE Group1Role
    ADD MEMBER UserB;

--grant object permissions to role
GRANT SELECT ON dbo.Table1 TO Group1Role;
GRANT SELECT, UPDATE, DELETE, INSERT ON dbo.Table1 TO Group1Role;
GRANT SELECT, UPDATE, DELETE, INSERT ON dbo.Table2 TO Group1Role;
GO

--Create another group of users that will allow them to SELECT, UPDATE, DELETE and INSERT INTO all three of the aforementioned tables
USE YourDatabase;

--create role with members
CREATE ROLE Group2Role;
ALTER ROLE Group2Role
    ADD MEMBER UserC;
ALTER ROLE Group1Role
    ADD MEMBER UserD;

--grant object permissions to role
GRANT SELECT, UPDATE, DELETE, INSERT ON dbo.Table1 TO Group2Role;
GRANT SELECT, UPDATE, DELETE, INSERT ON dbo.Table2 TO Group2Role;
GRANT SELECT, UPDATE, DELETE, INSERT ON dbo.Table3 TO Group2Role;
GO

Method 2

What you can do in sql server is to create a group of permission.

They are called Database Roles. You can use pre-build roles or create some new.

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15

About the group of users you can’t with the sql authentication but if you use the windows authentication you can relay on local user groups (uased as login in sql server) or active directory user groups (add them as login in sql server)

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-login?view=sql-server-ver15

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply