0 Replies Latest reply on Sep 24, 2014 6:58 PM by realworldis

    SolarWinds Service Account - Permissions to SQL Server Database

    realworldis

      Below is a script to grant the SolarWinds service account permissions to SQL Server databases.  It is a more modern version of the script available at AppInsight for SQL Requirements and Permissions

       

      Compared to the original script provided by SolarWinds, the new script:

      • Only adds logins and users if they don’t already exist.  This way, the script runs without errors if successful.  Any errors are legitimate issues to be corrected
      • Uses the more modern ‘CREATE USER’ vs. the old ‘sp_adduser’.  The latter uses the old SQL 6.5 construct where a schema was created for every user and has been removed as of SQL 2014

      (I’ve sent a support request to SolarWinds to see if the permission ‘CONNECT ANY DATABASE’ starting with SQL Server 2012 is supported.  If so, for a SQL Server 2012 server, that permission would eliminate the need to create the SolarWinds account as a user in every database, and to continually circle back to servers to make sure that any new databases have the SolarWinds account granted connect permissions.)

       

      Hope this Helps!

       

      USE [master]

      GO

      --Create the Login

      IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'Domain\AppInsightUser')

      CREATE LOGIN [Domain\AppInsightUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

      GO

      GRANT VIEW SERVER STATE TO "Domain\AppInsightUser"

      GRANT VIEW ANY DEFINITION TO "Domain\AppInsightUser"

      --grant certain permission in master

      IF NOT EXISTS (SELECT * FROM sys.sysusers WHERE name = 'Domain\AppInsightUser')

      CREATE USER [Domain\AppInsightUser] FOR LOGIN [Domain\AppInsightUser]

      GRANT EXECUTE ON xp_readerrorlog TO "Domain\AppInsightUser"

      --grant certain permission in msdb

      USE msdb

      IF NOT EXISTS (SELECT * FROM sys.sysusers WHERE name = 'Domain\AppInsightUser')

      CREATE USER [Domain\AppInsightUser] FOR LOGIN [Domain\AppInsightUser]

      EXEC sp_addrolemember N'db_datareader', N'Domain\AppInsightUser'

      --for each db, if the database is not a system database and the account doesn't already exist

      --does the account need access to tempdb and model?

      EXECUTE sp_MSforeachdb 'USE [?];

      IF EXISTS (SELECT * FROM sys.databases

      WHERE (is_distributor = 1 OR name NOT IN (''master'', ''model'', ''tempdb'', ''msdb''))

      AND name = ''?'') AND NOT EXISTS (SELECT * FROM sys.sysusers WHERE name = ''Domain\AppInsightUser'')

      CREATE USER [Domain\AppInsightUser] FOR LOGIN [Domain\AppInsightUser]

      '