In my earlier articles I wrote about many new enhancements in SQL Server 2012. Today, I will discuss the new enhancement in the permission area that includes Availability group, Schema, Search property list & Server categories.
The simplest way to query permissions is using sys.fn_builtin_permissions.
Given below is the script.
SELECT * FROM sys.fn_builtin_permissions('');
Above script will give you the complete list of permissions in SQL Server, but I need only the new permissions shipped in SQL Server 2012, so I compared the SQL Server 2008 R2 permissions with SQL Server 2012 and got the given below new permissions shipped with SQL Server 2012.
|
S. No |
Class Description |
Permission Name |
Covering Permission Name |
Parent Class Description |
Parent Covering Permission Name |
|
1 |
AVAILABILITY GROUP |
ALTER |
CONTROL |
SERVER |
ALTER ANY AVAILABILITY GROUP |
|
2 |
AVAILABILITY GROUP |
CONTROL |
|
SERVER |
CONTROL SERVER |
|
3 |
AVAILABILITY GROUP |
TAKE OWNERSHIP |
CONTROL |
SERVER |
CONTROL SERVER |
|
4 |
AVAILABILITY GROUP |
VIEW DEFINITION |
CONTROL |
SERVER |
VIEW ANY DEFINITION |
|
5 |
SCHEMA |
CREATE SEQUENCE |
ALTER |
DATABASE |
CONTROL |
|
6 |
SEARCH PROPERTY LIST |
ALTER |
CONTROL |
DATABASE |
ALTER ANY FULLTEXT CATALOG |
|
7 |
SEARCH PROPERTY LIST |
CONTROL |
|
DATABASE |
CONTROL |
|
8 |
SEARCH PROPERTY LIST |
REFERENCES |
CONTROL |
DATABASE |
REFERENCES |
|
9 |
SEARCH PROPERTY LIST |
TAKE OWNERSHIP |
CONTROL |
DATABASE |
CONTROL |
|
10 |
SEARCH PROPERTY LIST |
VIEW DEFINITION |
CONTROL |
DATABASE |
VIEW DEFINITION |
|
11 |
SERVER |
ALTER ANY AVAILABILITY GROUP |
CONTROL SERVER |
|
|
|
12 |
SERVER |
ALTER ANY EVENT SESSION |
CONTROL SERVER |
|
|
|
13 |
SERVER |
ALTER ANY SERVER ROLE |
CONTROL SERVER |
|
|
|
14 |
SERVER |
CREATE AVAILABILITY GROUP |
ALTER ANY AVAILABILITY GROUP |
|
|
|
15 |
SERVER |
CREATE SERVER ROLE |
ALTER ANY SERVER ROLE |
|
|
|
16 |
SERVER ROLE |
ALTER |
CONTROL |
SERVER |
ALTER ANY SERVER ROLE |
|
17 |
SERVER ROLE |
CONTROL |
|
SERVER |
CONTROL SERVER |
|
18 |
SERVER ROLE |
TAKE OWNERSHIP |
CONTROL |
SERVER |
CONTROL SERVER |
|
19 |
SERVER ROLE |
VIEW DEFINITION |
CONTROL |
SERVER |
VIEW ANY DEFINITION |
I will discuss the above permissions in my future articles in detail.
SQL Server Portal 
Leave a Reply