The script below is provided as an example of a script that can be used to add the LOGbinder service account to all databases that have a name starting with 'WSS_Content' as a user and set that user to the SPDataAccess role.
Please change 'LAB\lbspsvc' in the first line to the LOGbinder service account.
DECLARE @username VARCHAR(100) = 'LAB\lbspsvc'
DECLARE @MyCursor CURSOR;
DECLARE @MyField NVARCHAR(128);
DECLARE @sqlstatement VARCHAR(512);
BEGIN
SET @MyCursor = CURSOR FOR
SELECT [name] FROM master.sys.databases WHERE [name] LIKE 'WSS_Content%'
OPEN @MyCursor
FETCH NEXT FROM @MyCursor INTO @MyField
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlstatement =
'USE ' + @MyField + '; ' +
'CREATE USER [' + @username + '] FOR LOGIN [' + @username + ']' + '; ' +
'EXEC sp_addrolemember ''SPDataAccess'', ''' + @username + ''''
PRINT @sqlstatement
EXEC (@sqlstatement)
PRINT ''
FETCH NEXT FROM @MyCursor INTO @MyField
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
END;
On a scale of 1-5, please rate the helpfulness of this article
Optionally provide private feedback to help us improve this article...
Thank you for your feedback!