Product:
Microsoft SQL server 2016
Microsoft Windows 2012 server
Issue:
How add read access to all tables in all databases on one server to a user?
Solution:
Add a AD user as a login to the SQL server in SSMS. https://www.guru99.com/sql-server-create-user.html
In our example we use User3, but you change it to your login-name.
EXEC sp_MSforeachdb ' Use ?; Create user User3 for login User3; Grant select on database :: ? to User3 '
In the string after the sp_MSforeachdb command, the character ? is replaced by each database.
Use ?, this will move focus to this database, and execute the command after to this database.
Create user User3 for login User3, adds the user name to the database and connect it with the login user.
If the user3 already is in the database, then above will not work. Then run the command again without the Create user… part.
Grant select on database :: ? to User3, will give this user READ access to all object in the database. Even new tables and views.
The grant select on a database, will give that user full read access to the database tables and views.
More information:
https://www.mssqltips.com/sqlservertip/6702/sql-server-windows-authentication-with-users-and-groups/
https://www.techonthenet.com/sql_server/users/create_user.php
https://www.tutorialsteacher.com/sqlserver/grant-permissions-to-user
https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/
https://www.mssqltips.com/sqlservertip/1476/how-to-read-log-file-in-sql-server-using-tsql/
To grant SELECT permissions on the whole database use this:
USE <MY_DATABASE>
GRANT SELECT ON DATABASE :: <MY_DATABASE> TO <MY_USER>
where
<MY_USER>is user<MY_DATABASE>is database name
Granting permissions on schema doesn’t help, simple reason: if new schemas occur the user will not have permissions.
Granting permissions on all objects doesn’t for the same reason, the user will not have permissions on new objects created after GRANT event.