User shouldn't be able to run but can in production

  • krypto69

    SSChampion

    Points: 13506

    HI,

    I have a user that an A/D account that has no access to a production DB. But I caught that user today running a query on my production server!

    If I look at that user, he has no roles, and no access! He does have read/write on other DB's but not the DB I caught him on.

    How can I figure out how he is getting in?

  • Jon.Morisi

    SSChampion

    Points: 12846

    An old audit, but it still works great:

    http://www.mssqltips.com/sqlservertip/1818/script-to-auto-generate-a-security-report-for-your-sql-server-instance/[/url]

  • Grant Fritchey

    SSC Guru

    Points: 396716

    Look for A/D groups that do have access that he belongs to.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • benjamin.reyes

    SSCertifiable

    Points: 5249

    Are you sure that user doesn't have access to accounts they shouldn't?

    Your environment should have some sort of IP assignment control in the domain and IP filtering in the firewalls. There are numerous ways to watch/record/audit/alert on traffic and login attempts by IP or Machine Name to various layers of your sql box.

  • Ed Wagner

    SSC Guru

    Points: 286987

    The first thing is to figure out how they were connected when running the query.

    They could have been using a SQL login they should or should not have.

    They count be using their own AD account, or someone else's AD account. If it's their own account, you can check what AD roles the account has by using a DOS command:

    net user username /domain

    If they're using an AD account other than their own, you have a completely different problem on your hands. That's typically a pretty major offense.

  • VastSQL

    SSC-Dedicated

    Points: 35906

    Try this in your SQL

    xp_logininfo 'Domain\account' the account might be in some groups

  • krypto69

    SSChampion

    Points: 13506

    OKay I checked every single group and he is not getting in via group membership.

    I also know that he is using his domain account (domain\user).

    When I run - xp

    account name type privilege mapped login name permission path

    domain\user user user domain\user NULL

    Can't figure out how he is getting in so I setup a trigger to alert me when he logs in. Not sure what else to do next.

  • Grant Fritchey

    SSC Guru

    Points: 396716

    It's not magic. It has to be something. Either a SQL login that he has the password for, or he's in a group that's in a group that has access. Also check all the roles on the server and the databases, not just the logins.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Gail Shaw

    SSC Guru

    Points: 1004484

    If he's using his domain account, then either his account has been granted access, or he's a member of a group that has been granted access. Check the logins, check which are AD groups, who their members are.

    And make sure you don't have something like <domain>\Authenticated Users as a group granted login access.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Ed Wagner

    SSC Guru

    Points: 286987

    Also consider the possibility of different logins. They could be using a SQL login, their own AD account or a different AD account.

  • Jon.Morisi

    SSChampion

    Points: 12846

    What evidence do you have exactly?

    Your user could be using impersonation to execute as another user.

  • Lowell

    SSC Guru

    Points: 323463

    i use this to enumerate all the members that belong to groups that have been granted access from AD:

    i've found that some groups cannot be enumerated int he cases where you have trusts between two domains, and the group is from the foreign domain, or the local group contains foreign domain members.

    IF OBJECT_ID('tempdb.dbo.#tmp') IS NOT NULL

    DROP TABLE #tmp

    CREATE TABLE [dbo].[#TMP] (

    [ACCOUNT NAME] NVARCHAR(256) NULL ,

    [TYPE] VARCHAR(8) NULL ,

    [PRIVILEGE] VARCHAR(8) NULL ,

    [MAPPED LOGIN NAME] NVARCHAR(256) NULL ,

    [PERMISSION PATH] NVARCHAR(256) NULL )

    DECLARE @groupname NVARCHAR(256)

    DECLARE c1 CURSOR FOR SELECT name FROM master.sys.server_principals WHERE type_desc = 'WINDOWS_GROUP' AND CHARINDEX('$',name) = 0

    OPEN c1

    FETCH NEXT FROM c1 INTO @groupname

    WHILE @@FETCH_STATUS <> -1

    BEGIN

    BEGIN TRY

    INSERT INTO #tmp([ACCOUNT NAME],[TYPE],[PRIVILEGE],[MAPPED LOGIN NAME],[PERMISSION PATH])

    EXEC master..xp_logininfo @acctname = @groupname,@option = 'members' -- show group members

    END TRY

    BEGIN CATCH

    DECLARE @ErrorSeverity INT,

    @ErrorNumber INT,

    @ErrorMessage NVARCHAR(4000),

    @ErrorState INT

    SET @ErrorSeverity = ERROR_SEVERITY()

    SET @ErrorNumber = ERROR_NUMBER()

    SET @ErrorMessage = ERROR_MESSAGE()

    SET @ErrorState = ERROR_STATE()

    PRINT 'Msg ' + convert(varchar,@ErrorNumber) + ' Level ' + convert(varchar,@ErrorSeverity) + ' State ' + Convert(varchar,@ErrorState)

    PRINT @ErrorMessage

    END CATCH

    FETCH NEXT FROM c1 INTO @groupname

    END

    CLOSE c1

    DEALLOCATE c1

    SELECT * FROM [#TMP]

    --WHERE [MAPPED LOGIN NAME] = 'mydomain\Lowell'

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Jeff Moden

    SSC Guru

    Points: 997215

    Lowell (2/24/2016)


    i use this to enumerate all the members that belong to groups that have been granted access from AD:

    i've found that some groups cannot be enumerated int he cases where you have trusts between two domains, and the group is from the foreign domain, or the local group contains foreign domain members.

    IF OBJECT_ID('tempdb.dbo.#tmp') IS NOT NULL

    DROP TABLE #tmp

    CREATE TABLE [dbo].[#TMP] (

    [ACCOUNT NAME] NVARCHAR(256) NULL ,

    [TYPE] VARCHAR(8) NULL ,

    [PRIVILEGE] VARCHAR(8) NULL ,

    [MAPPED LOGIN NAME] NVARCHAR(256) NULL ,

    [PERMISSION PATH] NVARCHAR(256) NULL )

    DECLARE @groupname NVARCHAR(256)

    DECLARE c1 CURSOR FOR SELECT name FROM master.sys.server_principals WHERE type_desc = 'WINDOWS_GROUP' AND CHARINDEX('$',name) = 0

    OPEN c1

    FETCH NEXT FROM c1 INTO @groupname

    WHILE @@FETCH_STATUS <> -1

    BEGIN

    BEGIN TRY

    INSERT INTO #tmp([ACCOUNT NAME],[TYPE],[PRIVILEGE],[MAPPED LOGIN NAME],[PERMISSION PATH])

    EXEC master..xp_logininfo @acctname = @groupname,@option = 'members' -- show group members

    END TRY

    BEGIN CATCH

    DECLARE @ErrorSeverity INT,

    @ErrorNumber INT,

    @ErrorMessage NVARCHAR(4000),

    @ErrorState INT

    SET @ErrorSeverity = ERROR_SEVERITY()

    SET @ErrorNumber = ERROR_NUMBER()

    SET @ErrorMessage = ERROR_MESSAGE()

    SET @ErrorState = ERROR_STATE()

    PRINT 'Msg ' + convert(varchar,@ErrorNumber) + ' Level ' + convert(varchar,@ErrorSeverity) + ' State ' + Convert(varchar,@ErrorState)

    PRINT @ErrorMessage

    END CATCH

    FETCH NEXT FROM c1 INTO @groupname

    END

    CLOSE c1

    DEALLOCATE c1

    SELECT * FROM [#TMP]

    --WHERE [MAPPED LOGIN NAME] = 'mydomain\Lowell'

    Absolutely awesome! You have no idea how much this is going to help me. You should write an article on it, Lowell. Seriously!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Summer90

    SSC-Dedicated

    Points: 32844

    Is it possible to run profiler and see what userid is connecting and running that query?

  • Jeff Moden

    SSC Guru

    Points: 997215

    Markus (2/25/2016)


    Is it possible to run profiler and see what userid is connecting and running that query?

    Agreed. Setting it up to find the name of the proc in the "text data" using RPC Completed and Batch Completed will be mostly benign for resource usage, as well.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply