2

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.

0

1 Answer 1

4

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.

6
  • Thank you I will give this a go and will let you know if it works Apr 16, 2020 at 12:28
  • You are welcome. If it answers your question please mark it as accepted so other readers can take advantage. Apr 16, 2020 at 13:11
  • It did work. Quite sad that it cannot be done at the Server role level as it means I have to add same permissions over and over to like 20+ groups rather than just assigning all those groups to a single role. Oh well, what is life without struggles :D Apr 16, 2020 at 13:18
  • I've tried looking online but couldn't find anything. It seems that SELECT ALL USER SECURABLES is something that has been recently introduced in SQL server but I cannot seem to find equivalent permission for INSERT, DELETE, EXECUTE and UPDATE. I assume such permissions don't exist? Apr 20, 2020 at 14:59
  • 1
    So far I know it only works for selects. Apr 20, 2020 at 20:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.