Sql-server – Grant Permissions to all databases using server role

permissionssql serversql-server-2016

I know this question has been asked many times but they are all 3+ years old so I'm going to try my luck by asking again.

I need to grant Read permissions to all databases on MSSQL server. I would like to achieve this using server roles.

I've created server role with following permissions

CREATE SERVER ROLE ReadOnlyAccess 
GO
GRANT VIEW ANY DEFINITION TO ReadOnlyAccess
Go
GRANT VIEW ANY DATABASE TO ReadOnlyAccess
GO
GRANT VIEW SERVER STATE TO ReadOnlyAccess
GO
GRANT CONNECT SQL TO ReadOnlyAccess;
GO

Assigning user to this role allows me to connect to the server but not expand any of the databases. I would of thought that 'GRANT VIEW ANY DATABASE' would be the one that gives those rights but boy was I wrong…

Is there a way to achieve this without granting db_datareader database role on each DB for the user? New databases are constantly being created so doing this manually would be quite a nightmare.
I'm guessing that creating a job that runs every few hours would do the trick but I would still rather have it done via Server Role.

Any ideas? I'm using SQL 2016 and above.

Best Answer

Besides reading my answer look at this Q&A by Aaron Bertrand

How to create a read-only server role on SQL Server 2012?

This solution will work for SQL Server 2014 and above.

CREATE LOGIN [AccountNeedPermission] FROM WINDOWS WITH DEFAULT_DATABASE=[master];
GO
GRANT CONNECT ANY DATABASE to [AccountNeedPermission];
GO
GRANT SELECT ALL USER SECURABLES to [AccountNeedPermission];
GO
GRANT VIEW ANY DEFINITION to [AccountNeedPermission];
GO

Reason your solution will not work Server roles cannot be granted permission on database-level securables.