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

Product:

Microsoft SQL server 2016

Issue:

How log start and stop times of SP in your database?

Suggested solution:

Create a table to store time values, and create a SP that you call from other SP to update the table.

Create the log table:

CREATE TABLE [dbo].[_log_logged](
[LogID] [BIGINT] NULL,
[starttime] [DATETIME] NULL,
[endtime] [DATETIME] NULL,
[iscomplete] [INT] NULL,
[runstatus] [INT] NULL,
[durationInSeconds] [INT] NULL,
[whatProcess] [VARCHAR](100) NULL
) ON [PRIMARY]
GO

Create the SP to call to create log values:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[_logger] @LogID [INT], @StartTime [DATETIME],@EndTime [DATETIME], @WhatProcess [VARCHAR](100), @NewLogID [INT] OUT 
AS BEGIN 
SET NOCOUNT ON;
DECLARE @RunTimeSeconds INT,
@RunStatus INT,
@IsComplete INT,
@NewLogRowID INT,
@myDuration NUMERIC(20,10)

IF @LogID IS NULL
SET @LogID = 0

IF @LogID = 0
BEGIN 
SET @RunStatus = 1
SET @IsComplete = 0

SET @LogID = (SELECT ISNULL((MAX(LogID)),0) + 1 AS maxID FROM _log_logged)
INSERT INTO _log_logged(LogID
,StartTime
,runStatus
,IsComplete
,WhatProcess
)
SELECT @LogID,
@StartTime, 
@RunStatus,
@IsComplete,
@WhatProcess

END

ELSE IF @LogID != 0
BEGIN
SET @RunStatus = 0
SET @IsComplete = 1
UPDATE a
SET EndTime = @EndTime,
IsComplete = @IsComplete,
RunStatus = @RunStatus,
whatProcess = @WhatProcess,
DurationInSeconds= DATEDIFF(SECOND,@StartTime, @EndTime) 
FROM _log_logged a
WHERE a.LogID = @LogID

END

SET @NewLogID = @LogID
END
GO

Add below code to your SP, to enable them to log values to the above table.

-- =============================================
-- Author: 
-- Create date: 
-- Description: 
-- =============================================
CREATE PROCEDURE [dbo].[USP_ThisIsTheTest]
AS
BEGIN

SET NOCOUNT ON;
declare @StartTime datetime = GETDATE();
declare @EndTime datetime 
declare @logid int
DECLARE @NewLogID INT
DECLARE @whatProcessText VARCHAR(100) = 'Your name of the sp to be logged';


EXEC [dbo].[_logger] @LogID = 0
,@StartTime = @StartTime
,@EndTime = NULL
,@whatProcess = @whatProcessText
,@NewLogID = @NewLogID OUTPUT

-- =============================================
-- Here should your code be for the process to execute


WAITFOR DELAY '00:00:10.000'


-- =============================================
-- Log the end of the sp process with this call:

SET @EndTime = GETDATE()
EXEC [dbo].[_logger] @LogID = @NewLogID, @StartTime = @StartTime ,@EndTime = @EndTime ,@whatProcess = @whatProcessText ,@NewLogID = @NewLogID OUTPUT


END

Run the SP to test the log table.

The result can look like this:

More information:

SLEEP Command in Sql Server

https://www.mssqltips.com/sqlservertip/2003/simple-process-to-track-and-log-sql-server-stored-procedure-use/

https://www.sqlservercentral.com/articles/logging-and-error-handling-for-sql-stored-procedures

https://www.tech-recipes.com/database/sql-server-error-logging-and-reporting-within-a-stored-procedure/