By: K. Brian Kelley | Updated: 2010-04-13 | Comments | Related: > Security
Problem
I know that in SQL Server 2000 and below, you could assign permissions against objects like tables, views, and stored procedures. I'm hearing in SQL Server 2005 and 2008 there's a new security model called securables which allow for nestable permissions. How does this work?
Solution
SQL Server 2005 did include a new model called securables. A securable is basically anything within SQL Server that can be assigned a permission against. There is also a special type of securable called a scope. A scope is a securable that can contain other securables. If you think of the concept of folders from Windows, it's like nested folders. This applies with respect to permissions, as well, and it's how we get nested permissions. There are three scopes:
- Server
- Database
- Schema
If I assign a permission on a scope, like a particular schema (which contains the objects we're more used to working with like tables and stored procedures), and the permission is applicable at a lower level, such as a table, then that permission is implicitly applied at the lower level. This is the same idea as inherited permission in the case of Windows folders. If I assign Read & Execute permissions on a folder, then it applies to the subfolder as well (unless inheritance is broken). This can be taken further. For instance, I can apply a SELECT permission at the database level and it'll apply to all the schemas within the database (implicit) and then all the objects contained within those schema to which a SELECT statement is applicable.
USE MSSQLTips;
GO
CREATE USER TestSecurables WITHOUT LOGIN;
GO
CREATE SCHEMA HelpDesk;
GO
CREATE PROCEDURE HelpDesk.SomeProc
AS
BEGIN
SELECT 'Success!';
END;
GO If we attempt to execute the stored procedure as the user, we will get an error.
USE MSSQLTips;
GO
-- This should fail. The user has no applicable permissions
EXECUTE AS USER = 'TestSecurables';
GO
EXECUTE HelpDesk.SomeProc;
GO
REVERT;
GO That's because the user does not have permission to execute the stored procedure. Normally in this case we would be issuing a GRANT EXECUTE statement against the stored procedure in order to give the user access. But if we give it at the schema level, then any stored procedures that are contained by that schema are affected.
-- Grant permissions at the schema level
GRANT EXECUTE ON SCHEMA::HelpDesk TO TestSecurables;
GO And now when we execute the stored procedure, it should work. But a key point is that explicit DENY permissions still work. So, for instance, if we issue a DENY on the stored procedure itself, it will block access:
-- But DENY still trumps.
DENY EXECUTE ON HelpDesk.SomeProc TO TestSecurables;
GO And once again, if we attempt to execute the stored procedure, we will fail. So while the user gets an EXECUTE implicitly from the schema level, the DENY directly on the object blocks the ability to call the stored procedure. So from this example we can conclude several things:
- Securables are anything within SQL Server where permissions can be assigned.
- Securables that can contain other securables are called scopes.
- Scopes can also contain other securables.
- A permission applied at a higher securable level applies implicitly at lower levels.
- DENY permissions still trump access, regardless of permissions at other levels.
Next Steps
About the author
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2010-04-13
