-1

We have an sql server (Say SQL1) instance with 3 databases (Say Db1,db2 and db3 are my db names).

now I need to restrict the users from login into DB1.

So I have created the DDL logon trigger on that instance(SQL1)

the following script was used for creation of DDL trigger and some table

--Step1: Create Audit Table
USE master
GO

IF OBJECT_ID('DBO.LOGINAUDITTABLE') IS NOT NULL
    DROP TABLE DBO.LOGINAUDITTABLE

CREATE TABLE DBO.LOGINAUDITTABLE (
    ID INT IDENTITY(1, 1) PRIMARY KEY
    ,[PROGRAM_NAME] NVARCHAR(128)
    ,[HOST_NAME] NVARCHAR(200)
    ,[LOGIN_TIME] DATETIME
    ,[LOGIN_NAME] NVARCHAR(200)
    ,SESSION_ID INT
    ,[db_name] NVARCHAR(200)
    )
GO

IF EXISTS (
        SELECT *
        FROM master.sys.server_triggers
        WHERE parent_class_desc = 'SERVER'
            AND name = N'USER_RESTRICTION'
        )
    DROP TRIGGER USER_RESTRICTION ON ALL SERVER
GO

CREATE TRIGGER USER_RESTRICTION ON ALL SERVER
FOR LOGON AS

BEGIN
    DECLARE @data XML

    SET @data = EVENTDATA()

    DECLARE @AppName SYSNAME
        ,@LoginName SYSNAME
        ,@LoginType SYSNAME
        ,@HostName SYSNAME

    SELECT @AppName = [program_name]
    FROM sys.dm_exec_sessions
    WHERE session_id = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')

    SELECT @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname')
        ,@LoginType = @data.value('(/EVENT_INSTANCE/LoginType)[1]', 'sysname')
        ,@HostName = @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'sysname')

    INSERT INTO master.dbo.loginAuditTable (
        [PROGRAM_NAME]
        ,[HOST_NAME]
        ,[LOGIN_TIME]
        ,[LOGIN_NAME]
        ,SESSION_ID
        ,[db_name]
        )
    SELECT @AppName
        ,es.[host_name]
        ,es.[login_time]
        ,es.[LOGIN_NAME]
        ,@@SPID
        ,db.name
    FROM sys.dm_exec_sessions es
    JOIN sys.databases db ON db.database_id = es.database_id
    WHERE (
            es.host_name LIKE 'def%'
            AND es.login_name NOT LIKE 'abc\%'
            )
        AND session_id = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')
END;

the above was created by using this reference.

when I cross checked the table DBO.LOGINAUDITTABLE, the expected data was inserted in all columns except [db_name]. Every time only master values was inserted in the table even i have connected to Db1. So to get the database name do we need to modify any thing in the above procedure.

but when we check in the sys.dm_exec_sessions for that particular session I have seen database id which relates to DB1

please find the results below

enter image description here

Edited

Note: before blocking the users I need to get some information, hence I created the trigger with only insert statement. the blocking logic will be taken once the procedure is giving the desired results.

Edited2 Updated the trigger

IF EXISTS ( SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name = N'USER_RESTRICTION' ) DROP TRIGGER USER_RESTRICTION ON ALL SERVER GO

CREATE TRIGGER USER_RESTRICTION ON ALL SERVER
FOR LOGON AS

BEGIN
    DECLARE @data XML

    SET @data = EVENTDATA()

    DECLARE @AppName SYSNAME
        ,@LoginName SYSNAME
        ,@LoginType SYSNAME
        ,@HostName SYSNAME

    SELECT @AppName = [program_name]
    FROM sys.dm_exec_sessions
    WHERE session_id = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')

    SELECT @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname')
        ,@LoginType = @data.value('(/EVENT_INSTANCE/LoginType)[1]', 'sysname')
        ,@HostName = @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'sysname')
IF OBJECT_ID('tempdb..#sp_wh') IS NOT NULL
DROP TABLE #sp_wh
CREATE TABLE #sp_wh (
[SPID] INT
,[Status] VARCHAR(10)
,[Login] VARCHAR(200)
,[HostName] VARCHAR(200)
,[BlkBy] VARCHAR(10)
,[DBName] VARCHAR(200)
,[Command] VARCHAR(max)
,[CPUTime] INT
,[DiskIO] INT
,[LastBatch] VARCHAR(1000)
,[ProgramName] VARCHAR(1000)
,[SPID1] INT
,[REQUESTID] INT
);

insert into #sp_wh
exec sp_who2

declare @db_name varchar(300)

set @db_name=(select top 1 [DBName] from #sp_wh where [SPID]=@data.value('(/EVENT_INSTANCE/SPID)[1]', 'int'))
    INSERT INTO master.dbo.loginAuditTable (
        [PROGRAM_NAME]
        ,[HOST_NAME]
        ,[LOGIN_TIME]
        ,[LOGIN_NAME]
        ,SESSION_ID
        ,[db_name]
        )
    SELECT @AppName
        ,es.[host_name]
        ,es.[login_time]
        ,es.[LOGIN_NAME]
        ,@@SPID
        ,db.name
    FROM sys.dm_exec_sessions es
    JOIN sys.databases db ON db.database_id = es.database_id
    WHERE (
            es.host_name LIKE 'def%'
            AND es.login_name NOT LIKE 'abc\%'
            )
        AND session_id = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')
END;
  • I don't see how logging or auditing is going to restrict anything. Are you gonna check it every minute and chase after users who 'broke your rules'? Either don't give everyone the same User account to login with, or use Windows Authentication and restrict their access as described here. – Peter B Sep 12 '18 at 8:46
  • @PeterB, i have updated my question, please check – Smart003 Sep 12 '18 at 9:22
  • I have posted my work down here. there is an issue while executing in another server, that will be tracked in some other ticket – Smart003 Sep 17 '18 at 7:56
1

now I need to restrict the users from login into DB1.

Simple solution: Don't give them access to that database.

You need to grant Users (or, preferably Groups that contain Users) access to the things that they need to use, including databases.
If your Users should only be accessing DB2 and DB3, then that's all you should give them.

My suspicion is that you've handed out much broader privileges (like SysAdmin) and are now trying to claw back any kind of control over your databases.

  • Thanks Phill, we thought of going the suggested solution but we were thinking instead of removing access to the database we want to go with ddl trigger for the denying the access to the db – Smart003 Sep 12 '18 at 11:00
  • And if someone "accidentally" disabled that Trigger? Sure, you can create a complicated system to track the movements of the fox once it's inside your hen-house but it's far easier (and, therefore, cheaper and more reliable) to simply bolt the doors so it can't get in in the the first place. – Phill W. Sep 12 '18 at 11:15
  • I will partially agree with you Phill, basically except few people, no one will aware of that trigger at my place. We even thought of going for restricted users but for that db_owner permission should be given. instead of giving additional users or additional access , I'm looking for the alternate solution. My intention was there will no additional things should be performed on the users or roles. just creating some thing should restrict the access to a particular databases – Smart003 Sep 14 '18 at 11:54
0

the following script was working which used to get the current database name and tested in one server, desired results were achieved.

USE master
GO

IF OBJECT_ID('DBO.LOGINAUDITTABLE') IS NOT NULL
    DROP TABLE DBO.LOGINAUDITTABLE

CREATE TABLE DBO.LOGINAUDITTABLE (
    ID INT IDENTITY(1, 1) PRIMARY KEY
    ,[PROGRAM_NAME] NVARCHAR(128)
    ,[HOST_NAME] NVARCHAR(200)
    ,[LOGIN_TIME] DATETIME
    ,[LOGIN_NAME] NVARCHAR(200)
    ,SESSION_ID INT
    ,[db_name] NVARCHAR(200)
    )
GO

IF EXISTS (
        SELECT *
        FROM master.sys.server_triggers
        WHERE parent_class_desc = 'SERVER'
            AND name = N'USER_RESTRICTION'
        )
    DROP TRIGGER USER_RESTRICTION ON ALL SERVER
GO

CREATE TRIGGER USER_RESTRICTION ON ALL SERVER
FOR LOGON AS

BEGIN
    DECLARE @data XML

    SET @data = EVENTDATA()

    DECLARE @AppName SYSNAME
        ,@LoginName SYSNAME
        ,@LoginType SYSNAME
        ,@HostName SYSNAME

    SELECT @AppName = [program_name]
    FROM sys.dm_exec_sessions
    WHERE session_id = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')

    SELECT @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname')
        ,@LoginType = @data.value('(/EVENT_INSTANCE/LoginType)[1]', 'sysname')
        ,@HostName = @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'sysname')

    INSERT INTO master.dbo.loginAuditTable (
        [PROGRAM_NAME]
        ,[HOST_NAME]
        ,[LOGIN_TIME]
        ,[LOGIN_NAME]
        ,SESSION_ID
        ,[db_name]
        )
    SELECT @AppName
        ,es.[host_name]
        ,es.[login_time]
        ,es.[LOGIN_NAME]
        ,@@SPID
        ,original_db_name()
    FROM sys.dm_exec_sessions es
    WHERE (
            es.host_name LIKE 'def%'
            AND es.login_name NOT LIKE 'abc\%'
            )
        AND session_id = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')
END;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.