Grant User Access to All Databases

This article can help those who need to grant permissions for existing user to any database (except systems databases).

You can use my Script which includes Cursor!

Your existing user insert to Variable @User. In this example I grant permission for user who has "db_datareader" and "db_datawriter". If you want to only grant permission on db_datareader, just modify the script and delete this string:

EXEC sp_addrolemember N''db_datawriter'', ' + @User + '

At the end there must be ' - apostrophe behind the semicolon!

The result will look like this:

Then you can verify in login properties!

As you can see, User got permission (db_datareader) for all databases except master, model, msdb and tempdb.

I hope that this example helps!

  • Grey Twitter Icon
  • Grey Facebook Icon
  • Grey LinkedIn Icon

© 2020 All rights reserved. | Filip Holub

  • Twitter Social Icon
  • Facebook Social Icon
  • LinkedIn Social Icon