HomeLOGbinder for SP KBHow ToHow To Set SPDataAccess on Large Number of Content Databases

3.6. How To Set SPDataAccess on Large Number of Content Databases

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;

This page was: Helpful | Not Helpful