How give access to specific things in SQL

Product:

Microsoft SQL server 2016

Issue:

How do I give access only to a table?

Solution:

Ensure the user exist in the database. Enter below SQL query to create a role (RESTRICTED_SELECT), and set the SELECT to a table (TABLE_NAME) for that role. Last add the user (USER_NAME) to the role.  Replace values with your user and table names.

Or better add a AD group to the role, then the IT department can add people to that AD group to get access to the table.

CREATE ROLE RESTRICTED_SELECT
GO
GRANT SELECT ON TABLE_NAME TO RESTRICTED_SELECT
GO
SP_ADDROLEMEMBER RESTRICTED_SELECT , USER_NAME

If i want give access to a function, for instance the user should be able to see Active Monitor from SSMS enter below SQL query.

USE master;
CREATE SERVER ROLE SQLMonitor ;
GRANT VIEW SERVER STATE TO SQLMonitor ;
GO

To view the Data File I/O pane, besides VIEW SERVER STATE, the login must be granted CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION permissions.

To kill a process, it’s necessary to be a member of the sysadmin role.

To add the user to a server role enter:

sp_addsrvrolemember USER_NAME, SQLMonitor

More Information:

https://www.sqlshack.com/sql-server-activity-monitor/

https://www.codeproject.com/Tips/1103206/SQL-Server-Grant-Permission-to-Particular-Table

https://www.guru99.com/sql-server-create-user.html

https://sqlserverplanet.com/dba/using-sp_addrolemember