Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE [master]
- IF NOT EXISTS
- (SELECT name
- FROM master.sys.server_principals
- WHERE name = 'Domain/Login')
- BEGIN
- CREATE LOGIN [Domain/Login] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
- END
- SET NOCOUNT ON
- DECLARE @user_to_be_added sysname
- DECLARE @sqltext nvarchar(max) = N''
- -- change below to your user to be added .. make sure that the login is already existing
- SET @user_to_be_added = 'Domain/Login'
- SELECT @sqltext += char(10)+ 'use '+quotename(name)+';'+char(10)+ 'CREATE USER '+quotename(@user_to_be_added)+' FOR LOGIN '+quotename(@user_to_be_added)
- FROM sys.databases where state_desc ='ONLINE' AND databaseproperty(name,'isReadOnly') = 0
- SELECT @sqltext
- --- once above looks good with the output uncomment below line
- --EXEC sp_executesql @sqltext;
- SpConfigure
- CustomErrors
- Credentials
- DatabaseMail
- LinkedServers
- Logins
- LoginPermissions
- SystemTriggers
- DatabaseOwner
- AgentCategory
- AgentOperator
- AgentAlert
- AgentProxy
- AgentSchedule
- AgentJob
- SET NOCOUNT ON
- DECLARE @user_to_be_added sysname
- DECLARE @sqltext nvarchar(max) = N''
- -- change below to your user to be added .. make sure that the login is already existing
- SET @user_to_be_added = 'Domain/Login'
- SELECT @sqltext += char(10)+ 'use '+quotename(name)+';'+char(10)+ 'CREATE USER '+quotename(@user_to_be_added)+' FOR LOGIN '+quotename(@user_to_be_added)
- FROM sys.dm_hadr_availability_replica_states ars
- inner join sys.databases dbs ON ars.replica_id = dbs.replica_id
- where dbs.state_desc ='ONLINE' AND databaseproperty(dbs.name,'isReadOnly') = 0
- and ars.role_desc = 'PRIMARY' -- we want to filter only Primary AG where dbs are writable.
- if (@sqltext <> '')
- begin
- select @sqltext
- end
- else
- begin
- print 'There is nothing to print - @sqltext is blank'
- end
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement