Sql-server – Regarding SQL Server DENY permission on a table

permissionssql serversql-server-2005

RDBMS: SQL Server 2005

I am connecting to SQL Server through Windows Authentication.
My account(domain\me) belong to a group, "domain\alert"

The problem is that, even after denying a select permission on a table(actually all permissions),
I could still access that table on a new connection.

deny select on dbo.perm to [domain\me]
GO

And one thing to note is that, the group i belong to, "domain\alert" is a dbo of database the table is located.

As far as I know, deny permission takes precedence over grant.

What permission can still be granting me a permission to select the perm table?

Best Answer

From Books Online's Permissions Hierarchy: SQL Server 2005 Permissions Hierarchy http://i.msdn.microsoft.com/ms191465.dd24bb63-ab23-4f72-906f-ebc348fc64d8(en-US,SQL.90).gif
It looks like the Fixed database role takes precedence over the permissions given to a Windows Domain Login. I also was under the impression that a DENY would always take precedence over a GRANT, but I guess Fixed database role dbo trumps all.

Related Topic