ALL PRIVILEGES – The Misleading Permissions Shortcut

Introduction

GRANT ALL PRIVILEGES ON ... is a common shortcut to give a database principal full access to the securable in question. It is part of the SQL standard and implemented by most RDMSs on the market today. However, what ALL means is different from vendor to vendor. For only very few vendors ALL actually means "all" and SQL Server is one of the ones that leave a gap.

SQL Server 2000 had a significantly simpler security model and at that time ALL actually included all permissions that could be granted on the specified securable. With the enhancements to the permission model in SQL 2005 Microsoft had to make a decision to either change the behavior of existing code or deviate from the standard.

Significantly altering the behavior of existing code is not an idea that would have sat well with their customers, especially as we are talking about security related functionality. Therefore Microsoft went with the second option and deviated from the standard.

Because of that deviation from the standard and from what you would expect this functionality to do, Microsoft decided to deprecate this feature. It is possible that they are planning to reintroduce it after a waiting period to match the standard again, but that has not been announced yet.

Deprecation Warning

Because the ALL PRIVILEGES feature has been deprecated, you should not include this feature in new development and plan on removing it from the existing code base.

ALL PRIVILEGES Example

Just for completeness I am going to include a short example that shows that ALL really does not mean "all" anymore. Let's start with granting ALL PRIVILEGES on a procedure to TestUser1:

Now TestUser1 should be able to everything to and with this procedure. Let's check:

While the procedure executes with no problems, the quest for the procedure definition comes back empty handed.

For TestUser1 to be able to see the procedure definition, we need to also grant the VIEW DEFINITION privilege:

With that permission in place the OBJECT_DEFINITION() function returns the desired result:

Summary

ALL PRIVILEGES is a shortcut defined in the SQL standard that was intended to allow to quickly grant all available privileges on a securable to a principal. However, since the redesign of the SQL Server security model with SQL Server 2005 this feature does not perform the expected action anymore and has therefore been deprecated.