Discovering the Obvious: SQL Rights Setup Read Only, Write, and/or Read/Write

Here is a query I use to lock down users hitting all my databases with Read Only rights:

DROP LOGIN [YOUR_USER]
GO

-- ADD USERS
CREATE LOGIN [YOUR_USER] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
go
-- add the user to each database
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''YOUR_USER'')
BEGIN
CREATE USER [YOUR_USER] FOR LOGIN [YOUR_USER]
PRINT ''User Created in '' + DB_NAME()
END
ELSE
PRINT ''User Already Existed in '' + DB_NAME() '
go

-- add the user to each database db_datareader role
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''YOUR_USER'')
BEGIN
exec sp_addrolemember ''db_datareader'',''YOUR_USER''
PRINT ''User added to role in '' + DB_NAME()
END
ELSE
PRINT ''User does not exist in '' + DB_NAME() '
go

-- grant view any definition to user
USE master
go
GRANT VIEW ANY DATABASE TO [YOUR_USER]
PRINT 'Server wide permission granted'

Go

If you want to add writer use this query

-- Add Write ability to user for each database

-- add the user to each database db_datawriter role
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''YOUR_USER'')
BEGIN
exec sp_addrolemember ''db_datawriter'',''YOUR_USER''
PRINT ''User added to role in '' + DB_NAME()
END
ELSE
PRINT ''User does not exist in '' + DB_NAME() '
go

Sometimes users require more access than just read or write. Like to update stored procedures, etc..

-- Add the user to each database as db_owner role

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''YOUR_USER'')

BEGIN

exec sp_addrolemember ''db_owner'',''YOUR_USER''

PRINT ''User added to role in '' + DB_NAME()

END

ELSE

PRINT ''User does not exist in '' + DB_NAME() '