CODEWITHSUNDEEP
sql-serverssisuser-permissions
Darron Chapman
Darron Chapman

Reputation: 3

Restricting a user to only have access in a ssis package

We are running SQL Server 2016. For in-house political reasons, I can't restrict access the way I want to. Assume that I can't change the premise.

We have a user that is used in SSIS packages. Unfortunately, some devs are logging directly into the db with ssms using this user. I need to prevent this without changing the password or something. What I need is to be able to allow a user access to the database ONLY if it is running from an SSIS package and NOT if it is coming in any other way.

I am not looking for other suggestions of how to fix this issue. I understand most of them already, I am stuck because of management decisions that I cannot change.

Can anyone tell me how to restrict a user in such a way?

Upvotes: 0

Views: 345

Answers (1)

billinkc
billinkc

Reputation: 61249

An approach is to use a LOGON trigger

A first blush approach might be to reject any process that look's like the SSMS application

CREATE OR ALTER TRIGGER logon_developer_check
ON ALL SERVER
FOR LOGON
AS 
BEGIN
    IF (ORIGINAL_LOGIN() = 'triskydeveloper'
    and EXISTS
    (
    SELECT
        *
    FROM
        sys.sysprocesses AS S
    WHERE
        S.spid = @@SPID
        AND S.program_name LIKE 'Microsoft SQL Server Management%'
    )
    BEGIN
        ROLLBACK
    END
END

But developers, being devious little buggers, will then write their own .NET application or use SQLCMD so you'd fall into a rat race trying to identify all the program_names that might show up.

I would instead look at the hostname column on sys.sysprocesses - if the connection isn't coming from the server itself, just reject it. Unless you have to deal with developers able to RDP onto the server.

Oh and if you mangle the logon trigger and it's rejecting everything, use SQLCMD and the dedicated admin console, DAC, and 

sqlcmd.exe -S localhost -d master -Q "DISABLE TRIGGER ALL ON SERVER" -A

Upvotes: 1

Related Questions