SQL Server nested securable permissions

By:   |   Updated: 2010-04-13   |   Comments   |   Related: More > 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


Last Updated: 2010-04-13


get scripts

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools