-- ADD USERS
-- add the user to each database
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''Domain\NT User'')
BEGIN
CREATE USER [Domain\NT User] FOR LOGIN [Domain\NT 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''Domain\NT User'')
BEGIN
exec sp_addrolemember ''db_datareader'',''Domain\NT 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 [Domain\NT User]
PRINT 'Server wide permission granted'
/*
-- DROP USERS
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''Domain\NT User'')
BEGIN
DROP USER [Domain\NT User];
PRINT ''User dropped from '' + DB_NAME()
END
Else
PRINT ''User did not exist in '' + DB_NAME()
*/