I would like a script that will allow our web developers read only access to all databases on our SQL server.
Currently we are having to do this manually as we have no SQL expert / DB Admin.
Would somebody be able to help me out by creating a script that will add there windows user to SQL and Grant Read only access to all databases.
Occasionally they will need read / write access on a specific database so we can change this manually but I would like a script that we can schedule overnight that will change the access back if we forget.
Any help is much appreciated.
Regards
Best Answer
If you have a list of known usernames, you can store them in a table somewhere:
Now you can create a variety of scripts to ensure that:
for each relevant database:
db_datareader
roleSELECT
permission on thedbo
schemaHere is a start to solve 1. (I'll come back and address the others as I have time later today):
For the rest, this makes some basic assumptions: you always elevate privileges using database-level roles, rather than explicit GRANT/DENY, server role membership or AD group membership. Like the table of developers, you also want to have somewhere a list of the databases you want to affect:
Now you can build a script that will drop the user from each database, create the entire list from scratch, and add each user only to the db_datareader role. You may want to customize the script if you want to grant explicit rights on a certain schema or set of objects.
This is very rough, and lots of things can make it break. I could have combined the two selects into a single query but I think double-nested dynamic SQL was complex enough without adding conditional string concatenation in there as well.