For this end of the year, I decided to present you the new permissions on SQL Server 2012.

How to have a list of all permissions ?

It's very simple with this query:

SQL 2008 R2

SQL 2012

With this comparison, you can see that there are 19 new permissions in SQL Server 2012.

To have a great overview, I prefer use this query ordering by the class description:

SELECT class_desc,count(*) as Permission_Number FROM sys.fn_builtin_permissions('') group by class_desc;

Just 2 classes have new permissions: SERVER and SCHEMA. But 3 new classes appeared: AVAILABILITY GROUP, SERVER ROLE and SEARCH PROPERTY LIST.

Let's go for the details...

Class SERVER

Query:

Result:

These 5 new permissions are for the new features AlwaysOn, User-Defined Server Role and Extended Events.

Class SCHEMA

Query:SELECT * FROM sys.fn_builtin_permissions('') where class_desc='SERVER';

Result:

This new permission is for the new feature Sequence

Class AVAILABILITY GROUP

Query:SELECT * FROM sys.fn_builtin_permissions('') where class_desc='AVAILABILITY GROUP';

Result:

These 4 new permissions are for the new High Availability feature AlwaysOn.

Class SERVER ROLE

Query:SELECT * FROM sys.fn_builtin_permissions('') where class_desc='SERVER ROLE';

Result:

These 4 new permissions are for the new feature User defined Server Role.

You can found an article of this feature here

Class SEARCH PROPERTIES LIST

Query:SELECT * FROM sys.fn_builtin_permissions('') where class_desc='SEARCH PROPERTIES LIST';

Result:

These 5 new permissions are for the enhancement of Full-text.

Search properties list is used to specify one or more search properties that you want to include in a full-text index.

Conclusion

19 new permissions for AlwaysOn, Sequence, User-defined Server Role, Extended Event and Search Properties List are created in this version of SQL Server.

This blog is to present you how to find permissions on SQL Server and I hope that can help you.