How To Set SPDataAccess on Large Number of Content Databases

: 4 Years Ago
: Tamas Lengyel
: How To

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;