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:

Grant Permission

Then you can verify in login properties!

Login User Mapping

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

I hope that this example helps!

RECENT POST