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.


  • SQL Server 2008 Standard Jun 27, 2012 at 11:24
  • Yep sorry - 'if we forget' meant that if we forgot to change permissions back to read only Jun 27, 2012 at 11:24
  • Ok, so not only do you need to add the read only permissions (in case they've been revoked or the user doesn't exist at all), you also need to take away any additional privileges you may have granted them in the meantime...? Jun 27, 2012 at 11:30
  • yeah so if possible we can specify in the script / seperate file the names of our web developers the script should ensure that these users have read only access to all databases. We will change there rights so they can write to the databases when requested but the overnight script should set this back to read only. Thankyou for your help so far. Jun 27, 2012 at 15:08
  • There are complications. While the developer has elevated permissions, they can potentially change objects, create objects that they now own, etc. This can get very complicated very quickly. My suggestion was going to be drop the user from each database and re-add them, but this is only going to work if privileges are always elevated in a known and controlled way (e.g. adding them to the db_datawriter role). Jun 27, 2012 at 15:11

1 Answer 1


If you have a list of known usernames, you can store them in a table somewhere:


INSERT dbo.WebDevelopers(name)
  SELECT N'YourDomain\User1'
  SELECT N'YourDomain\User2';

Now you can create a variety of scripts to ensure that:

  1. they exist as a login at the server level
  2. for each relevant database:

    • they exist as a user
    • they are in the db_datareader role
    • they have SELECT permission on the dbo schema

Here is a start to solve 1. (I'll come back and address the others as I have time later today):

DECLARE @sql NVARCHAR(MAX), @cr NCHAR(2) = CHAR(13) + CHAR(10);

SELECT @sql = N'';

SELECT @sql = @sql + @cr + 'CREATE LOGIN '
FROM dbo.WebDevelopers AS d
LEFT OUTER JOIN sys.server_principals AS p
ON d.name = p.name

PRINT @sql;
-- EXEC sp_executesql @sql;

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:

CREATE TABLE dbo.DeveloperDatabases(name SYSNAME PRIMARY KEY);

INSERT dbo.DeveloperDatabases(name)
  SELECT N'some_database'
  -- UNION ALL N'...';

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.

DECLARE @sql NVARCHAR(MAX), @cr NCHAR(2) = CHAR(13) + CHAR(10);

SELECT @sql = N'SELECT @sql2 = N'''';';

SELECT @sql = @sql + @cr + N'SELECT @sql2 = @sql2 + ''
  USE ' + QUOTENAME(name) + '; 
  DROP USER '' + QUOTENAME(d.name) + '';''
FROM dbo.WebDevelopers AS d
INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
ON d.name = p.name;'
FROM dbo.DeveloperDatabases;

SELECT @sql = @sql + @cr + N'SELECT @sql2 = @sql2 + ''
  USE ' + QUOTENAME(name) + '; 
  CREATE USER '' + QUOTENAME(d.name) + '' FROM LOGIN '' + QUOTENAME(d.name) + '';
  EXEC sp_addrolemember ''''db_datareader'''', '''''' + d.name + '''''';''
FROM dbo.WebDevelopers AS d
LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
ON d.name = p.name
WHERE p.name IS NULL;'
FROM dbo.DeveloperDatabases;


EXEC sp_executesql @sql, N'@sql2 NVARCHAR(MAX) OUTPUT', @sql2 OUTPUT;

PRINT @sql2;
-- EXEC sp_executesql @sql2;

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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.