Here are my notes for security enhancement in the SQL Server 2014.
- CONNECT ANY DATABASE: User can connect to all the databases. This is same as to add the user to all the database but with one simple grant instead of adding user multiple times.
- SELECT ALL USER SECURABLES: User can select all the objects including the objects in the system databases. This is same as to grant db_reader to the user on all the database but with one simple grant instead of adding user multiple times
- IMPERSONATE ANY LOGIN
Below is what it is looks like when user only have CONNECT ANY DATABASE privilege
Below is what it is looks like when user have CONNECT ANY DATABASE and SELECT ALL USER SECURABLES privilege . If user does not have CONNECT ANY DATABASE but only SELECT ALL USER SECURABLES, user is still not able to query the objects because it can not change the context to the specific database.
One existing permission CONTROL SERVER, if we grant this permission to the user, user would have ‘IMPERSONATE ANY LOGIN’, which means even user is not sysadmin, but they can impersonate as sysadmin and add themselves as sysadmin. The solution for this prior 2014 is we have to explicitly deny any impersonate when new sysadmin is added.
Obviously , this is not idea. From SQL 2014 onward, we can just