I have a stored procedure that finds all the existing databases and reads from a table in each one.
Is there a way I can give a login read access to all databases, and to all future databases i.e., I won't have to do anything when a new database is added?
Is there a server role that would work? Is there a way to make a SQL agent job add the permissions on any new databases? Or is there some other method?
Expand Security - Logins, then Right Click on login and then click Properties. Go to User Mapping tab and select the database on which you want to give permission and at bottom select db_datareader as shown below. Click Ok and you're done.
For new databases, add the user in the model database. This is used as the template for all new databases.
USE model
CREATE USER ... FROM LOGIN...
EXEC sp_addrolemember 'db_datareader', '...'
For existing databases, use sp_MSForEachDb
EXEC sp_MSForEachDb '
USE ?
CREATE USER ... FROM LOGIN...
EXEC sp_addrolemember ''db_datareader'', ''...''
'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With