A recurring need that I have seen is a means to grant a user or group of users access to all databases in one fell swoop. Recently, I shared an introductory article to this requirement. In this article, I will demonstrate how to easily grant permissions to all databases via the use of Server Roles.
When talking about Server Roles, I don’t mean the Fixed Server Roles. It would be crazy easy, insane, and stupid to just use the fixed server roles to grant users access to all databases. Why? Well, only two of the fixed server roles would cover the permission scope needed by most users to access a database – and use said database. Those roles are sysadmin and securityadmin.
The sysadmin role should be fairly obvious and is generally what every vendor and a majority of developers insists on having. We all know how dangerous and wrong that would be. The securityadmin fixed server role on the other hand is less obvious. That said, securityadmin can grant permissions and should therefore be treated the same as sysadmin. By no means do we ever want to grant access (as a shortcut / easy method) via these roles, that would be security-defeating.
The pre-requisite for there to be an easy button is to create your own Server-Level role. I demonstrated how to do this in a previous article and will glaze over it quickly again here.
IF NOT EXISTS ( SELECT name FROM sys.server_principals WHERE name = 'Gargouille' ) BEGIN CREATE LOGIN [Gargouille] WITH PASSWORD = N'SuperDuperLongComplexandHardtoRememberPasswordlikePassw0rd1!' , DEFAULT_DATABASE = [] , CHECK_EXPIRATION = OFF , CHECK_POLICY = OFF; END; --check for the server role IF NOT EXISTS ( SELECT name FROM sys.server_principals WHERE name = 'SpyRead' AND type_desc = 'SERVER_ROLE' ) BEGIN CREATE SERVER ROLE [SpyRead] AUTHORIZATION [securityadmin]; GRANT CONNECT ANY DATABASE TO [SpyRead]; END; USE master; GO IF NOT EXISTS ( SELECT mem.name AS MemberName FROM sys.server_role_members rm INNER JOIN sys.server_principals sp ON rm.role_principal_id = sp.principal_id LEFT OUTER JOIN sys.server_principals mem ON rm.member_principal_id = mem.principal_id WHERE sp.name = 'SpyRead' AND sp.type_desc = 'SERVER_ROLE' AND mem.name = 'Gargouille' ) BEGIN ALTER SERVER ROLE [SpyRead] ADD MEMBER [Gargouille]; END;
In this demo script, I have created a user and a server-level role, then added that user to the role. The only permission currently on the server-level role is “Connect Any Database”.
Now, let’s say we need to be able to grant permissions to all databases for this user to be able to read (that would be SELECT in SQL terms) data. The only thing I need to do for the role would be to make this permission change.
GRANT SELECT ALL USER SECURABLES TO [SpyRead];
That is a far simpler approach, right? Let’s see how it might look to add a user to the role from SQL Server Management Studio (SSMS).
Now, let’s test the permission to select from a database.
EXECUTE AS LOGIN = 'Gargouille'; GO USE []; GO -- no permissions on server state SELECT * FROM sys.dm_os_wait_stats; GO --Yet can select from any database SELECT USER_NAME() SELECT * FROM sys.objects REVERT
Clearly, you will need to change your database name unless by some extreme chance you also have a database by the name of
Now this is where it gets a little dicey. Suppose you wish to grant the delete option (not a super wise idea to be honest) to a user in every database. That won’t work with this method. You would need to grant those permissions on a per case basis.
Where this solution works best is for permissions that are at the server scope. Permissions at this scope include the things such as “Control Server”, “View any Definition”, “View Server State”, and “Select all USER Securables”. This isn’t a complete list but just enough to give you an idea. That said, how often do you really need to have a user be able to change data in EVERY database on a server? I certainly hope your security is not setup in such a fashion.
Suppose you decide to utilize the permission “SELECT ALL USER SECURABLES”, there is an additional feature that comes with it. This permission can be used to deny SELECT permission against all databases as well. As a bonus, it works to block sysadmins as well – sort of. It does deny the SELECT permission, unlike other methods, when applied to a sysadmin, however, any sysadmin worth their salt can easily revoke that permission because they have “CONTROL” server permission.
That said, it would be a worthwhile trick to play on your junior dbas to see what they do.
As Data Professionals, we are always trying to find more efficient ways of doing the job. Sometimes, against our best advice, we are required to find a more efficient way to give users access to more than they probably should have. This article demonstrates one method to easily grant READ access to all databases while still keeping the environment secure and hitting that chord of having done it more efficiently.
Interested in learning about some deep technical information? Check these out!
Want to learn more about your indexes? Try this index maintenance article or this index size article.
This is the fifth article in the 2020 “12 Days of Christmas” series. For the full list of articles, please visit this page.
The post How to Easily Grant Permissions to all Databases appeared first on SQLServerCentral.
I remember deciding to pursue my first IT certification, the CompTIA A+. I had signed…
Key takeaways The transformer architecture has proved to be revolutionary in outperforming the classical RNN…
Once we learn how to deploy an Ubuntu server, how to manage users, and how…
Key-takeaways: Clean code isn’t just a nice thing to have or a luxury in software projects; it's a necessity. If we…
While developing a web application, or setting dynamic pages and meta tags we need to deal with…
Software architecture is one of the most discussed topics in the software industry today, and…