Automations

Automatically Add User As Data Reader On All Databases

Here’s a quick script you can use to automatically add a user across all databases with Read Only ( Data Reader ) rights. Additionally; this script will remove the user ( if it exists ) from any of the other roles just as a precaution.

use master;
set nocount on
declare	@my_windows_user                varchar(50)
declare	@change_access_to_read_only	varchar(max)
set	@my_windows_user		= 'MyDomain\MyUserName'
set	@change_access_to_read_only	= ''
select	@change_access_to_read_only = @change_access_to_read_only +
'use [' + name + '];'																								+ char(10) + '
create user [' + @my_windows_user + '] for login [' + @my_windows_user + '];'							+ char(10) + '
exec master..sp_dropsrvrolemember @loginame = '''	+ @my_windows_user + ''',	@rolename	= ''bulkadmin'';'	+ char(10) + '
exec master..sp_dropsrvrolemember @loginame = '''	+ @my_windows_user + ''',	@rolename	= ''dbcreator'';'	+ char(10) + '
exec master..sp_dropsrvrolemember @loginame = '''	+ @my_windows_user + ''',	@rolename	= ''diskadmin'';'	+ char(10) + '
exec master..sp_dropsrvrolemember @loginame = '''	+ @my_windows_user + ''',	@rolename	= ''processadmin'';'	+ char(10) + '
exec master..sp_dropsrvrolemember @loginame = '''	+ @my_windows_user + ''',	@rolename	= ''securityadmin'';'	+ char(10) + '
exec master..sp_dropsrvrolemember @loginame = '''	+ @my_windows_user + ''',	@rolename	= ''serveradmin'';'	+ char(10) + '
exec master..sp_dropsrvrolemember @loginame = '''	+ @my_windows_user + ''',	@rolename	= ''setupadmin'';'      + char(10) + '
exec master..sp_dropsrvrolemember @loginame = '''	+ @my_windows_user + ''',	@rolename	= ''sysadmin'';'	+ char(10) + '
exec sp_addrolemember ''db_datareader'', '''		+ @my_windows_user + ''';'						+ char(10) + '
exec sp_droprolemember ''db_owner'', '''		+ @my_windows_user + ''';'					        + char(10) + char(10)					
from
      sys.databases
where
      name not in ('master', 'model', 'msdb', 'tempdb')

exec  (@change_access_to_read_only) --for xml path(''), type 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s