Post reply

Deny access to same table across all databases dynamically

  • July 20, 2017 at 5:17 pm

    #322772

    Does anyone have a creative method for denying access to a specific table (an audit table) for all users not in a specific group?

    Databases are created dynamically by an application and while I can go set deny to the object on each database for each user/group, I was hoping to find a good way to make it more automated.

    I was pondering a server level login trigger but didn't know if I could maybe do it with Database Auditing, Database level Security Policies or Server level Policy Management (none of which I've used before).

    Owner & Principal SQL Server Consultant
    Im Your DBA, Inc.
    https://www.imyourdba.com/

  • July 20, 2017 at 9:24 pm

    #1951996

    certainly possible, but something like that sounds like it needs to be part of the dynamic generation processes, and not a cleanup after the fact.
    the problem is we don't know what the existing permissions are...
    you could deny access to users who are in botht he good group and the bad groups very easily, resulting in no access for individuals who should have access

    there is an xp_logininfo function that you can use to seeall the groups a member is in ;
    eventually, you need to generate some commands form something :
    SELECT 'DENY SELECT,INSERT,UPDATE,DELETE ON dbo.PayrollTable TO '
    + QUOTENAME(name)
    FROM {somesource}
    something like this can help, but it does not handle nested groups

    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
    INSERT INTO #tmp([ACCOUNT NAME],[TYPE],[PRIVILEGE],[MAPPED LOGIN NAME],[PERMISSION PATH])
      EXEC master..xp_logininfo @acctname = @groupname,@option = 'members'  -- show group members
      FETCH NEXT FROM c1 INTO @groupname
      END
    CLOSE c1
    DEALLOCATE c1

    SELECT * FROM [#TMP]
    --WHERE [MAPPED LOGIN NAME] = 'mydomain\Lowell'

    --find anyone who is a group, so they don't need to exist as a distinct login
    select * from sys.server_principals p
      INNER JOIN #TMP t ON p.name = t.[Mapped Login Name]
      where type_desc = 'WINDOWS_LOGIN'

    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!

Viewing 2 posts - 1 through 1 (of 1 total)

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