Server level database permissions

4

February 14, 2017 by Kenneth Fisher

T-SQL Tuesday 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.

4 thoughts on “Server level database permissions

  1. Konstantin says:

    Hi, Kenneth, thanks for post. Also useful link about sql server permission: social.technet.microsoft.com/wiki/contents/articles/11842.sql-server-database-engine-permission-posters.aspx

  2. […] Server Level Database Permissions – Kenneth Fisher gives us a great reminder that server level database permissions exist and that they make our lives easier. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,627 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: