I am new to SQL Server, and I need to do something like the following:
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!
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='UserAP@ssw0rd';
CREATE LOGIN UserB WITH PASSWORD='UserBP@ssw0rd';
CREATE LOGIN UserC WITH PASSWORD='UserCP@ssw0rd';
CREATE LOGIN UserD WITH PASSWORD='UserDP@ssw0rd';
--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
LOGIN per individual User or just create a LOGIN per Active Directory Group that those Users already belong to. Presumably you'd only need two Active Directory Groups here for a total of two LOGINs, one for the more restrictive permissions and one for the less restrictive permissions. You can even use one of the existing Active Directory Groups like [Domain Users] if you wanted to assign default permissions for all Users on the domain.
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.
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)
