Windows login restriction to deny select

  • Hi all,

    I've got an interesting problem, and I'm not sure how to fix it. I need to deny select's on a specific table to everyone that connects to the DB. But I can't get it to stick. I have an application that connects via Integrated domain login, so I can set these permissions and then test and it should not allow this application to connect in it's current state. However, I can't get the DENY permissions to work.

    I used the following to deny everything to all user logins in my domain:

    DECLARE @SQL AS NVARCHAR(MAX)

    SET @SQL = ''

    SELECT

    @SQL = @SQL + CASE WHEN @SQL = N'' THEN N'' ELSE N'

    ' END + 'DENY SELECT ON dbo.[Table] TO [' + [name] + '];'

    FROM

    sys.server_principals

    WHERE

    [name] like SUBSTRING(SYSTEM_USER, 0, CHARINDEX('\',SYSTEM_USER)+1) + '%'

    PRINT @SQL

    EXEC (@SQL)

    Which executes the following dynamic sql:

    DENY SELECT ON dbo.[Table] TO [ACME\Domain Users];

    to deny SELECT for the domain groups. I then checked that this was denied using a script from Lowell

    SELECTCASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END

    + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)

    + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END

    + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name) COLLATE database_default

    + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END + ';' AS '--Object Level Permissions'

    FROMsys.database_permissions AS perm

    INNER JOIN

    sys.objects AS obj

    ON perm.major_id = obj.[object_id]

    INNER JOIN

    sys.database_principals AS usr

    ON perm.grantee_principal_id = usr.principal_id

    LEFT JOIN

    sys.columns AS cl

    ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

    ORDER BY perm.permission_name ASC, perm.state_desc ASC

    Which returned

    DENY SELECT ON [dbo].[Table] TO [Domain Users];

    DENY SELECT ON [dbo].[Table] TO [ACME\Domain Users];

    I then checked my credentials (I'm in the ACME\Domain Users group).

    EXEC master..xp_logininfo

    @acctname = 'ACME\venoym',

    @option = 'all'

    It shows the following:

    Account Name |type |privilege |mapped login name |permission path

    ACME\venoym user admin ACME\venoym ACME\Domain Users

    According to that, I should be barred from selecting from dbo.[Table]... but I'm still selecting that (in SSMS and in SQL Profiler)

    Any ideas?

  • Not sure I've understood correctly here, but if I have then you're a sysadmin on the instance - I assume you are since you need that, or something close it, to DENY all those permissions in the first place. So if you are a sysadmin, you can do anything in SQL Server - this overrides anything that has been denied to you.

    John

  • John Mitchell-245523 (3/1/2012)


    Not sure I've understood correctly here, but if I have then you're a sysadmin on the instance - I assume you are since you need that, or something close it, to DENY all those permissions in the first place. So if you are a sysadmin, you can do anything in SQL Server - this overrides anything that has been denied to you.

    John

    So, if I read you right, if you are in the sysadmin server role that overrides any DENY that is setup for a specific login? I would have thought DENY overrides any other permission...

  • Yes, you read me right. Sysadmin is sysadmin, and has irrevocable god-like powers on the instance.

    John

  • If you're trying to DENY a permission from an application service account in the sysadmin server role then that login probably should not be in the sysadmin role. You need to get more granular. Consider removing the login from the sysadmin role. If the login (i.e. application) has a legitimate need to perform server-level tasks you still have options after removing them from the sysadmin role, but ideally you will be able to manage all permissions the application needs at the database (or multiple databases) level.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 5 posts - 1 through 4 (of 4 total)

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