Server level database permissions
4February 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.
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
Great link! I actually already have that under my Study and Reference Materials page. (look under the posters heading). I do however need to update the page a bit!
Good evening, Kenneth. Another great link: https://github.com/ktaranov/sql-server-samples/tree/master/samples/features/security/permissions-posters (after they accept my pull request it will be in oreiginal repo here – https://github.com/Microsoft/sql-server-samples/tree/master/samples/features/security/permissions-posters, now it has typos pwermissions https://github.com/Microsoft/sql-server-samples/tree/master/samples/features/security/pwermissions-posters).
Also, Kenneth, do you receive my email? Any reply will be very helpful for me.
[…] Server Level Database Permissions – Kenneth Fisher gives us a great reminder that server level database permissions exist and that they make our lives easier. […]