How to Easily Grant Permissions to all Databases

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 Bad, the Bad, and the Ugly

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.

There is one more role that seems to be a popular choice – the public role. Visualize a child’s eyes rolling into the back of their head and you have my reaction to this option. This is the ugly of the options but cannot go without mentioning because I deal with vendors on a regular basis that continue to insist on doing things this way. This is not necessarily the easy method because you have to manually grant permissions to the public fixed server role, so this comes with some work but it is just flat stupid and lazy to grant all of these permissions to the public role. Here is an article on this absurd method.

Create Custom Server Roles for all DB Access

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).

After creating a custom server role, you will be able to see it from the login properties page and then add the login directly to the role from the gui. That makes the easy button just a little bit better.

Test it out

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 . Testing this will prove that the user can connect to the database and can also select data from that database.

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.

Caveat

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.

Put a bow on it

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 first appeared on SQL RNNR.