Advertisement
Guest User

Untitled

a guest
Feb 19th, 2019
71
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.83 KB | None | 0 0
  1. USE [master]
  2. IF NOT EXISTS
  3. (SELECT name
  4. FROM master.sys.server_principals
  5. WHERE name = 'Domain/Login')
  6. BEGIN
  7. CREATE LOGIN [Domain/Login] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
  8. END
  9.  
  10. SET NOCOUNT ON
  11. DECLARE @user_to_be_added sysname
  12. DECLARE @sqltext nvarchar(max) = N''
  13.  
  14. -- change below to your user to be added .. make sure that the login is already existing
  15. SET @user_to_be_added = 'Domain/Login'
  16. SELECT @sqltext += char(10)+ 'use '+quotename(name)+';'+char(10)+ 'CREATE USER '+quotename(@user_to_be_added)+' FOR LOGIN '+quotename(@user_to_be_added)
  17. FROM sys.databases where state_desc ='ONLINE' AND databaseproperty(name,'isReadOnly') = 0
  18.  
  19. SELECT @sqltext
  20.  
  21. --- once above looks good with the output uncomment below line
  22. --EXEC sp_executesql @sqltext;
  23.  
  24. SpConfigure
  25. CustomErrors
  26. Credentials
  27. DatabaseMail
  28. LinkedServers
  29. Logins
  30. LoginPermissions
  31. SystemTriggers
  32. DatabaseOwner
  33. AgentCategory
  34. AgentOperator
  35. AgentAlert
  36. AgentProxy
  37. AgentSchedule
  38. AgentJob
  39.  
  40. SET NOCOUNT ON
  41. DECLARE @user_to_be_added sysname
  42. DECLARE @sqltext nvarchar(max) = N''
  43.  
  44. -- change below to your user to be added .. make sure that the login is already existing
  45. SET @user_to_be_added = 'Domain/Login'
  46. SELECT @sqltext += char(10)+ 'use '+quotename(name)+';'+char(10)+ 'CREATE USER '+quotename(@user_to_be_added)+' FOR LOGIN '+quotename(@user_to_be_added)
  47. FROM sys.dm_hadr_availability_replica_states ars
  48. inner join sys.databases dbs ON ars.replica_id = dbs.replica_id
  49. where dbs.state_desc ='ONLINE' AND databaseproperty(dbs.name,'isReadOnly') = 0
  50. and ars.role_desc = 'PRIMARY' -- we want to filter only Primary AG where dbs are writable.
  51.  
  52. if (@sqltext <> '')
  53. begin
  54. select @sqltext
  55. end
  56. else
  57. begin
  58. print 'There is nothing to print - @sqltext is blank'
  59. end
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement