February 14, 2017 by Kenneth Fisher
Sometimes you have a requirement to grant permissions to every database on an instance. Historically this has required creating a user (database level principal) on each database and granting it the correct permission. And making sure to grant those permissions to the model database so that future databases have the correct permission as they are created. There is now a better way! And that makes this a perfect subject for this month’s T-SQL Tuesday. Matt Gordon (b/t) is our host this month with the subject Fixing Old Problems with Shiny New Toys.
So what’s our shiny new permissions toy? Well, over time we have been getting more and more server level permissions that affect database level objects. A lot of these can be really useful and I’m hoping we will see more and more of them.
Permissions to control a database
|Server Level Permission||Minimum Version||Definition|
|CREATE DATABASE||2012+||Pretty self explainitory. The ability to create new databases.|
|ALTER ANY DATABASE||2012+||Change database properties.|
|VIEW ANY DATABASE||2012+||Kind of like VIEW DEFINITION but for database properties.|
Permissions that affect permissions within every database
|Server Level Permission||Minimum Version||Database Equivilent|
|ADMINISTER BULK OPERATIONS||Required for bulk operations in any database|
|CONNECT ANY DATABASE||2014+||CONNECT|
|SELECT ALL USER SECURABLES||2014+||SELECT|
|VIEW ANY DEFINITION||2012+||VIEW DEFINITION|
So now if we want to grant someone read access to every database on the instance it’s as simple as creating the login (server level principal) and granting it CONNECT ANY DATABASE and SELECT ALL USER SECURABLES.
Some additional links:
SQL 2014 Learning Series 1: CONNECT ANY DATABASE
SQL 2014 Learning Series 2: SELECT ALL USER SECURABLES
New SQL Server 2014 Permissions: CONNECT ANY DATABASE
Is it safe to grant ADMINISTER BULK OPERATIONS
And of course the disclaimer: I’m by no means perfect, so if I missed anything let me know and I’ll add it to the list.