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:
SELECT * FROM sys.fn_builtin_permissions(”)
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:
SELECT * FROM sys.fn_builtin_permissions(”) where class_desc=’SERVER’;
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.:-D