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.