Permissions (Database Engine)

Every SQL Server securable has associated permissions that can be granted to a principal. Permissions in the Database Engine are managed at the server level assigned to logins and server roles, and at the database level assigned to database users and database roles. The model for SQL Database has the same system for the database permissions, but the server level permissions are not available. This topic contains the complete list of permissions. For a typical implementation of the permissions, see Getting Started with Database Engine Permissions.

The total number of permissions for SQL Server vNext and SQL Database is 237. Most permissions apply to all platforms, but some do not. For example server level permissions cannot be granted on SQL Database, and a few permissions only make sense on SQL Database. SQL Server 2016 exposed 230 permissions. SQL Server 2014 exposed 219 permissions. SQL Server 2012 exposed 214 permissions. SQL Server 2008 R2 exposed 195 permissions. The sys.fn_builtin_permissions topic specifies which topics are new in recent versions. The following graphic shows the permissions and their relationships to each other. Some of the higher level permissions (such as CONTROL SERVER) are listed many times. Click the image to download the Database Engine Permissions Poster in pdf format.

Once you understand the permissions, apply server level permissions to logins and database level permissions users with the GRANT, REVOKE, and DENY statements. For Example: