Hi Dears as we know SQL Server 2014 come with more new features and more improvements in the Performance today we will take about the new Feature in SQL Server 2014 in particular in SQL Server security SQL Server 2014 come with three new permission really amazing permission it will help any DBA in his work to can grant simple permission in All Databases in the server in the minimum time this operation it will not take from him one second lets go for the scenario and for the DEMO.
in SQL 2012 and we can see 31 permission whereas SQL 2014 has 34 permissions. These permissions are listed under Server properties > permissions tab.
New Permission in SQL Server 2014:
- CONNECT ANY DATABASE Permission.
- SELECT ALL USER Securables Permission.
- IMPERSONATE ANY LOGIN Permission.
Scenario:
Today if you have any customer or auditing company in your organization and need to give him access on your DB Server to can see all database and he can do expand for this databases plus he can read any data from this databases by new permissions in SQL Server 2014 we can do it by the fast and easy way.
DEMO :
1- Create User
USE [master]
GO
CREATE LOGIN [Mostafa] WITH PASSWORD=N’P@$$w0rd’
GO
2- CONNECT ANY DATABASE Permission.
use [master]
GO
GRANT CONNECT ANY DATABASE TO [Mostafa]
GO
3- SELECT ALL USER Securables Permission
use [master]
GO
GRANT SELECT ALL USER SECURABLES TO [Mostafa]
GO
For this “IMPERSONATE ANY LOGIN Permission” we need to explain to you the Problem case to know what the the benefit from this new permission to can use it in your environment. for more detail go to my Post : IMPERSONATE ANY LOGIN Permission.
