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