DENY AND REVOKE

Question

Hi,

What is the difference between DENY AND REVOKE in SQL SERVER 2005 Security?

Please explain with examples.

Answer 1

Hello,

GRANT gives explicit permissions, DENY denies explicit permissions and REVOKE removes GRANT/DENY permissions settings; see REVOKE Server Permissions (Transact-SQL)

---

Olaf Helper

Blog Xing

Answer 2

DENY prevents a principal from inheriting permissions from role or group membership.  REVOKE removes a previous GRANT or DENY permission.

See the SQL Server Books Online for details on DENY (http://msdn.microsoft.com/en-us/library/ms188338.aspx) and REVOKE (http://msdn.microsoft.com/en-us/library/ms187719.aspx).  Examples below.

USE tempdb;
GO
CREATE ROLE TestRole;
CREATE USER TestUser WITHOUT LOGIN;
ALTER ROLE TestRole
	ADD MEMBER TestUser;
GO
CREATE TABLE dbo.Test(Col1 int);
GO

--this fails because TestUser has no permissions on table dbo.Test
EXECUTE AS USER = 'TestUser';
GO
SELECT * FROM dbo.Test;
GO
REVERT;
GO

--grant permissions to user
GRANT SELECT ON dbo.Test TO TestUser;
GO

--this succeeds because TestUser now has SELECT permission
EXECUTE AS USER = 'TestUser';
GO
SELECT * FROM dbo.Test;
GO
REVERT;
GO

--remove permission from user
REVOKE SELECT ON dbo.Test FROM TestUser;
GO

--this fails because TestUser no longer has SELECT permission
EXECUTE AS USER = 'TestUser';
GO
SELECT * FROM dbo.Test;
GO
REVERT;
GO

--grant permissions to role
GRANT SELECT ON dbo.Test TO TestRole;
GO

--this succeeds because TestUser inherits SELECT permission due to role membership
EXECUTE AS USER = 'TestUser';
GO
SELECT * FROM dbo.Test;
GO
REVERT;
GO

--deny permissions to TestUser
DENY SELECT ON dbo.Test TO TestUser;
GO

--this fails because TestUser no longer inherits SELECT permission - deny takes precedence over grant
EXECUTE AS USER = 'TestUser';
GO
SELECT * FROM dbo.Test;
GO
REVERT;
GO

--remove deny permission
REVOKE SELECT ON dbo.Test FROM TestUser;
GO

--this succeeds because deny was removed; SELECT permission is again inherited
EXECUTE AS USER = 'TestUser';
GO
SELECT * FROM dbo.Test;
GO
REVERT;
GO

DROP TABLE dbo.Test;
DROP USER TestUser;
DROP ROLE TestRole;
GO

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/