Feeds:
Posts
Comments

Posts Tagged ‘sys.fn_builtin_permissions’

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.

Read Full Post »