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

Permissions that affect permissions within every database

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.

Filed under: Microsoft SQL Server, Security, SQLServerPedia Syndication, T-SQL Tuesday Tagged: microsoft sql server, security, T-SQL Tuesday