The built in SA login on a server has full access too all databases. Is there a way to have a login, that has read-only access to all databases, current and future databases? So like sa, but with readonly access?


You can grant db_datareader to any user (map to a login) that you want. This will address the "all actual databases".

For the future database, as all new databases are created based on the [model] database, if you create a user in the [model] DB that has the "db_Datareader" role, then when a new DB will be created, it will contains that user as well.

If your new database come from backup restore, then this won't work and you may want to look at something like a trigger.

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