Grant permission to all existing databases to a user
First make sure you pick the right role: https://msdn.microsoft.com/en-us/library/ms189121.aspx For existing databases use this
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
-- 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() */ |
For future ones use this
|
1 2 |
USE model EXEC sp_addrolemember N'db_owner', N'Domain\NT User' |
