SQL Server security, read-only default to new databases

Question

How do I set a Windows login on SQL Server so that it has read-only access to any database on the server, and will default to having read-only access to any subsequently added database.

I would prefer T-SQL code.

I am using SQL 2000

Answer 1

I would suggest you create a new group and make all user accounts belong to it. Then grant that role the db_datareader permission in all your databases.

To create a new server role called 'EVERYONE': EXEC sp_addrole 'EVERYONE'

...then put all user accounts into it (you can find the T-SQL to do this from this article, but you'll have to dig it out--my firewall won't let me post that fragment here!).

...then make the role a member of each database's db_datareader role:

EXEC sp_msforeachdb 'USE [?] EXEC sp_addrolemember [db_datareader], [EVERYONE]';

(*Note that for security reasons you probably do not want the full db_datareader permissions on the system databases like 'master', so you could might remove the EVERYONE group from those.*)

Now you just need to remember:

1. When you create a new user login, add it to the 'EVERYONE' role: EXEC sp_addrolemember [EVERYONE], [new_user_name]
2. When you create a new database, add 'EVERYONE' to the 'db_datareader' role: USE [new_db_name]; EXEC sp_addrolemember [db_datareader], [EVERYONE]

Answer 2

For new databases can you not just add the user/group into a read only role within the model database. When a new database is created this is used as the template.

Answer 3

The easiest solution is to add your user to the model database. All new databases 'inherit' from model, so if you add the user there, it will automatically be in all new ones:

use model
exec sp_adduser @Loginame = 'MyLogin', @name_in_db = 'MyUser'
exec sp_addrolemember @rolename = 'db_datareader', @membername = 'MyUser'

Note that this only works if you actually execute CREATE DATABASE. If you create new databases by restoring a backup (or CREATE then RESTORE) then the contents of the backup will overwrite whatever comes from model.

If your needs are more complex, write your own script or tool to create new databases, and just put the logic in there. You might want to do this anyway if you have to manage a large number of databases.