March 1, 2012 at 8:15 am
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?
March 1, 2012 at 8:24 am
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
March 1, 2012 at 8:33 am
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...
March 1, 2012 at 8:35 am
Yes, you read me right. Sysadmin is sysadmin, and has irrevocable god-like powers on the instance.
John
March 2, 2012 at 2:16 pm
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