Home / server / Questions / 59991
Accepted
dance2die
Asked: 2009-08-29 06:20:40 +0800 CST

Regarding SQL Server DENY permission on a table

  • 772

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?

sql-server sql-server-2005 permissions

1 Answers

  1. 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.

    • 2