Grant Permissions to all databases using server role

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.

Here is Solutions:

We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.

Solution 1

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.

Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply