SET NOCOUNT ON ;

    -- IMPORTANT!!!!! PLEASE READ BEFORE EXECUTING THE SCRIPT --
--  you MUST CREATE A WINDOWS AUTHENTICATED LOGIN CALLED 'dnbint\z-impvuser'
--  This dnbint\z-impvuser login doesn't need to be added to any database or given
--  any privilege.  The script will take care of that.

USE master

DECLARE @Imperva_user AS nvarchar(50) 
SET @Imperva_user = 'dnbint\z-impvuser'

DECLARE @Imperva_user_esc AS nvarchar(50)
set @Imperva_user_esc = replace(@Imperva_user,'''','''''')

-- =====================================================================
-- Check if the @Imperva_user user exists
-- =====================================================================
IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = @Imperva_user)
BEGIN
	PRINT ''
	PRINT '************************************************************************'
	PRINT '*** ERROR: Could not find the login: ''' + @Imperva_user + ''''
	PRINT '***        Please add the login and re-run this script.'
	PRINT '************************************************************************'
	PRINT ''
END
ELSE
BEGIN
	DECLARE @dbName AS nvarchar(256)
	DECLARE @memberName AS nvarchar(256)
	DECLARE @dbVer AS nvarchar(128)
	DECLARE @dbVerInt AS int

	SET     @dbVer = CAST(serverproperty('ProductVersion') AS nvarchar)
	SET     @dbVer = SUBSTRING(@dbVer, 1, CHARINDEX('.', @dbVer) - 1)
	SET     @dbVerInt = CAST(@dbVer AS int)  --db version in integer ( >=9 ) format

	/* ====== NOT REQUESTED BY IMPERVA ======
	IF (@dbVerInt < 11)
	BEGIN
	  -- This privilege is required to peform a specific MSSQL test.
	  -- Test name: SQL OLEDB disabled (DisallowAdhocAccess registry key) 
	  -- Procedure execute: EXEC master.dbo.sp_MSset_oledb_prop 
	  -- Purpose: To display provider property, not changing anything. 
	  PRINT '==> Granting  setupadmin server role for MSSSQL 2005/2008 '

	  EXEC master..sp_addsrvrolemember @loginame = @Imperva_user, @rolename = N'setupadmin'
  
	END
	ELSE  --for sql 2012 or above
	BEGIN
	  /* using newer syntax as sp_addsrvrolemember will be deprecated in future release */
	  EXEC ('ALTER SERVER ROLE [setupadmin] ADD MEMBER  [' + @Imperva_user + ']' )
	END
	========================================== */

	SELECT  @dbName = DB_NAME()
	PRINT '---------------------------------------------------------------------------'
	PRINT 'START ==> Database role creation for MSSQL version ' + @dbVer + ' on database ' + @dbName
	PRINT '---------------------------------------------------------------------------'
	--DROP TABLE #tmpImpervarolemem

	-- =====================================================================
	-- Find any members of the role if they exist
	-- =====================================================================
	CREATE TABLE #tmpImpervarolemem (membername nvarchar(256) NOT NULL)
	PRINT '==> Find and back up any members in the role ''imperva_role'''
	INSERT INTO #tmpImpervarolemem
	SELECT distinct	grantee.name 
	FROM     sys.database_role_members ro, 
		 sys.database_principals db_role, 
		 sys.database_principals grantee 
	WHERE	ro.role_principal_id = db_role.principal_id
		and ro.member_principal_id = grantee.principal_id
		and db_role.name = 'imperva_role'

	-- =====================================================================
	--  Drop the Role Members If they exist
	-- =====================================================================
	IF EXISTS (SELECT 1 FROM #tmpImpervarolemem)
	BEGIN
	  PRINT '==> Dropping the imperva_role role members on: ' + @dbName
	  DECLARE DropCursor CURSOR FOR SELECT membername from #tmpImpervarolemem
	  OPEN DropCursor
	  FETCH DropCursor INTO @memberName
	  WHILE @@Fetch_Status = 0
	   BEGIN
	   set @memberName = replace(@memberName,'''','''''')
		 PRINT '==> Dropping member: ''' + @memberName + ''''
		exec ('EXEC sp_droprolemember ''imperva_role'', ''' + @memberName + ''' ;')
		FETCH DropCursor INTO @memberName
	   END
	  CLOSE DropCursor
	  DEALLOCATE DropCursor
	END

	-- =====================================================================
	-- Drop the imperva_schema schema if it exists
	-- =====================================================================
	IF EXISTS(SELECT 1 FROM sys.schemas where name = 'imperva_schema')
	BEGIN
	  PRINT '==> Dropping the schema imperva_role on: ' + @dbName
	  DROP SCHEMA [imperva_schema]
	END

	-- =====================================================================
	-- Drop the imperva_role role if it exists
	-- =====================================================================
	IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'imperva_role' and type = 'R')
	BEGIN
	  PRINT '==> Dropping the role imperva_role on: ' + @dbName
	  DROP ROLE [imperva_role]
	END
	-- =====================================================================
	-- Create the imperva_role role
	-- =====================================================================
	PRINT '==> Creating the role imperva_role on: ' + @dbName
	--exec sp_addrole 'imperva_role'
	CREATE ROLE [imperva_role]

	-- =====================================================================
	-- Re-add the dropped members
	-- =====================================================================
	IF EXISTS (SELECT 1 FROM #tmpImpervarolemem)
	BEGIN
		PRINT '==> Re-adding the role members on: ' + @dbName
		DECLARE DropCursor CURSOR FOR SELECT membername FROM #tmpImpervarolemem
		OPEN DropCursor
		FETCH DropCursor               INTO @memberName
		WHILE @@Fetch_Status = 0
		BEGIN
			--Escape username with single quote by replacing it with double quote. 
			SET @memberName = replace(@memberName,'''','''''')
			PRINT '==> Re-adding member: ''' + @memberName + ''''
			EXEC('EXEC sp_addrolemember ''imperva_role'', ''' + @memberName + ''' ;')
			FETCH DropCursor           INTO @memberName
		END
		CLOSE DropCursor
		DEALLOCATE DropCursor
	END

	PRINT '---------------------------------------------------------------------------'
	PRINT 'END  ==> Database role creation for MSSQL version ' + @dbVer + ' on database ' + @dbName
	PRINT '---------------------------------------------------------------------------'
	PRINT ' '
	PRINT ' '
	PRINT '---------------------------------------------------------------------------'
	PRINT 'START ==> Granting common select & execute privilages on database: ' + @dbName
	PRINT '---------------------------------------------------------------------------'
	-- =====================================================================
	-- Required Permissions on 'master' database on MSSQL 2008 and Higher
	-- =====================================================================
	-- In addition to the permissions required for any database, the following permissions are needed on the 'master'
	-- database:

	PRINT '==> ASSESSMENT - Granting common SELECT privileges'
	GRANT SELECT ON INFORMATION_SCHEMA.COLUMNS			TO imperva_role
	GRANT SELECT ON INFORMATION_SCHEMA.TABLE_PRIVILEGES TO imperva_role
	GRANT SELECT ON sys.configurations					TO imperva_role
	GRANT SELECT ON sys.credentials						TO imperva_role
	GRANT SELECT ON sys.databases						TO imperva_role
	GRANT SELECT ON sys.fn_trace_geteventinfo			TO imperva_role
	GRANT SELECT ON sys.fn_trace_getinfo				TO imperva_role
	GRANT SELECT ON sys.linked_logins					TO imperva_role
	GRANT SELECT ON sys.master_files					TO imperva_role
	GRANT SELECT ON sys.master_key_passwords			TO imperva_role
	GRANT SELECT ON sys.server_permissions				TO imperva_role
	GRANT SELECT ON sys.server_principals				TO imperva_role
	GRANT SELECT ON sys.server_role_members				TO imperva_role
	GRANT SELECT ON sys.servers							TO imperva_role
	GRANT SELECT ON sys.service_broker_endpoints		TO imperva_role
	GRANT SELECT ON sys.soap_endpoints					TO imperva_role
	GRANT SELECT ON sys.sql_logins						TO imperva_role
	GRANT SELECT ON dbo.spt_values						TO imperva_role
	GRANT SELECT ON dbo.sysconfigures					TO imperva_role
	GRANT SELECT ON dbo.syscurconfigs					TO imperva_role
	GRANT SELECT ON dbo.sysdatabases					TO imperva_role
	GRANT SELECT ON dbo.syslogins						TO imperva_role
	GRANT SELECT ON INFORMATION_SCHEMA.SCHEMATA			TO imperva_role

	PRINT '==> ASSESSMENT - Granting common EXECUTE privileges'
	GRANT EXECUTE ON sys.fn_isrolemember				TO imperva_role
	GRANT EXECUTE ON sys.sp_dbfixedrolepermission		TO imperva_role
	GRANT EXECUTE ON sys.sp_executesql					TO imperva_role
	GRANT EXECUTE ON sys.sp_get_distribuTOr				TO imperva_role
	GRANT EXECUTE ON sys.sp_helpdbfixedrole				TO imperva_role
	GRANT EXECUTE ON sys.sp_helpreplicationdboption		TO imperva_role
	GRANT EXECUTE ON sys.sp_helprolemember				TO imperva_role
	GRANT EXECUTE ON sys.sp_helprotect					TO imperva_role
	GRANT EXECUTE ON sys.sp_helpsrvrolemember			TO imperva_role
	GRANT EXECUTE ON sys.sp_helpuser					TO imperva_role
	GRANT EXECUTE ON sys.sp_server_info					TO imperva_role
	GRANT EXECUTE ON sys.xp_loginconfig					TO imperva_role

	PRINT '==> USER RIGHTS - Granting common SELECT privileges'
	GRANT SELECT ON MASTER.sys.databases				TO imperva_role
	GRANT SELECT ON MASTER.sys.server_principals		TO imperva_role
	GRANT SELECT ON MASTER.sys.server_permissions		TO imperva_role
	GRANT SELECT ON MASTER.sys.server_role_members		TO imperva_role

	PRINT '---------------------------------------------------------------------------'
	PRINT 'END   ==> Granting common select & execute privilages on database: ' + @dbName
	PRINT '---------------------------------------------------------------------------'

	USE msdb
	SELECT  @dbName = DB_NAME()
	PRINT '---------------------------------------------------------------------------'
	PRINT 'START ==> Database role creation for MSSQL version ' + @dbVer + ' on database ' + @dbName
	PRINT '---------------------------------------------------------------------------'
	--DROP TABLE #tmpImpervarolemem

	-- =====================================================================
	-- Find any members of the role if they exist
	-- =====================================================================
	TRUNCATE TABLE #tmpImpervarolemem
	PRINT '==> Find and back up any members in the role ''imperva_role'''
	INSERT INTO #tmpImpervarolemem
	SELECT distinct	grantee.name 
	FROM     sys.database_role_members ro, 
		 sys.database_principals db_role, 
		 sys.database_principals grantee 
	WHERE	ro.role_principal_id = db_role.principal_id
		and ro.member_principal_id = grantee.principal_id
		and db_role.name = 'imperva_role'

	-- =====================================================================
	--  Drop the Role Members If they exist
	-- =====================================================================
	IF EXISTS (SELECT 1 FROM #tmpImpervarolemem)
	BEGIN
	  PRINT '==> Dropping the imperva_role role members on: ' + @dbName
	  DECLARE DropCursor CURSOR FOR SELECT membername from #tmpImpervarolemem
	  OPEN DropCursor
	  FETCH DropCursor INTO @memberName
	  WHILE @@Fetch_Status = 0
	   BEGIN
	   set @memberName = replace(@memberName,'''','''''')
		 PRINT '==> Dropping member: ''' + @memberName + ''''
		exec ('EXEC sp_droprolemember ''imperva_role'', ''' + @memberName + ''' ;')
		FETCH DropCursor INTO @memberName
	   END
	  CLOSE DropCursor
	  DEALLOCATE DropCursor
	END

	-- =====================================================================
	-- Drop the imperva_role schema if it exists
	-- =====================================================================
	IF EXISTS(SELECT 1 FROM sys.schemas where name = 'imperva_schema')
	BEGIN
	  PRINT '==> Dropping the schema imperva_schema on: ' + @dbName
	  DROP SCHEMA [imperva_schema]
	END

	-- =====================================================================
	-- Drop the imperva_role role if it exists
	-- =====================================================================
	IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'imperva_role' and type = 'R')
	BEGIN
	  PRINT '==> Dropping the role imperva_role on: ' + @dbName
	  DROP ROLE [imperva_role]
	END

	-- =====================================================================
	-- Create the imperva_role role
	-- =====================================================================
	PRINT '==> Creating the role imperva_role on: ' + @dbName
	--exec sp_addrole 'imperva_role'
	CREATE ROLE [imperva_role]

	-- =====================================================================
	-- Re-add the dropped members
	-- =====================================================================
	IF EXISTS (SELECT 1 FROM #tmpImpervarolemem)
	BEGIN
		PRINT '==> Re-adding the role members on: ' + @dbName
		DECLARE DropCursor CURSOR FOR SELECT membername FROM #tmpImpervarolemem
		OPEN DropCursor
		FETCH DropCursor               INTO @memberName
		WHILE @@Fetch_Status = 0
		BEGIN
			--Escape username with single quote by replacing it with double quote. 
			SET @memberName = replace(@memberName,'''','''''')
			PRINT '==> Re-adding member: ''' + @memberName + ''''
			EXEC('EXEC sp_addrolemember ''imperva_role'', ''' + @memberName + ''' ;')
			FETCH DropCursor           INTO @memberName
		END
		CLOSE DropCursor
		DEALLOCATE DropCursor
	END

	PRINT '---------------------------------------------------------------------------'
	PRINT 'END  ==> Database role creation for MSSQL version ' + @dbVer + ' on database ' + @dbName
	PRINT '---------------------------------------------------------------------------'
	PRINT ''
	PRINT ''
	PRINT '---------------------------------------------------------------------------'
	PRINT 'START ==> Granting select & execute privilages on database: ' + @dbName
	PRINT '---------------------------------------------------------------------------'
	-- =====================================================================
	-- Required Permissions on 'msdb' Database on MSSQL 2008 and Higher
	-- =====================================================================
	-- In addition to the permissions required for any database, the following permissions are required on the 'msdb'
	-- database:

	PRINT '==> ASSESSMENT - Granting common SELECT privileges'
	GRANT SELECT ON msdb.sys.database_principals			TO imperva_role
	GRANT SELECT ON msdb.dbo.sysjobs						TO imperva_role
	GRANT SELECT ON msdb.dbo.sysproxies						TO imperva_role
	GRANT SELECT ON msdb.dbo.sysproxysubsystem				TO imperva_role
	GRANT SELECT ON msdb.dbo.sysjobhistory					TO imperva_role
	GRANT SELECT ON msdb.dbo.syssubsystems					TO imperva_role
	GRANT SELECT ON msdb.dbo.sysproxylogin					TO imperva_role

	PRINT '==> ASSESSMENT - Granting common EXECUTE privileges'
	GRANT EXECUTE ON msdb.dbo.sp_enum_login_for_proxy		TO imperva_role
	GRANT EXECUTE ON msdb.dbo.sp_enum_proxy_for_subsystem	TO imperva_role

	PRINT '---------------------------------------------------------------------------'
	PRINT 'END   ==> Granting select & execute privilages on database: ' + @dbName
	PRINT '---------------------------------------------------------------------------'

	DROP TABLE #tmpImpervarolemem

	-- =====================================================================
	-- ROLE CREATION ON APPLICATION DATABASES
	-- =====================================================================

	DECLARE @currentDB										AS nvarchar(128)
	DECLARE @executeString									AS nvarchar(max)
	DECLARE @executeString2									AS nvarchar(max)
	DECLARE @dbcounter										AS INT   
	SET @dbcounter = 0		

	-- =====================================================================
	-- Find out all application databases
	-- =====================================================================

	DECLARE DBCursor CURSOR STATIC FOR 
	SELECT name FROM sys.databases WHERE name NOT IN ('master', 'msdb')
	AND user_access <>  1		--not single user
	AND state = 0				--online
	AND is_read_only = 0		--not read only

	OPEN DBCursor
	FETCH NEXT FROM DBCursor INTO @dbName

	WHILE @@FETCH_STATUS = 0
	BEGIN

		SET @dbcounter = @dbcounter + 1 
		SET @currentDB =  @dbName 	                                     /* This is only for syntax like Use db_name as bracket would handle space and ' */
		SET @dbName = quotename(replace(@dbName, '''', ''''''))          /* replace ' with '' to escape the single quote that may exists in database name and SQL Injection DB name */
		SET @executeString = ''
		SET @executeString2 = ''

	-- =====================================================================
	-- Find any members of the role if they exist
	-- =====================================================================
		SET @executeString = @executeString + 
			'USE ' + quotename(@currentDB) + ' ' +
			'PRINT ''---------------------------------------------------------------------------'' ' +
			'PRINT ''START ==> Database role creation for MSSQL version ' + @dbVer + ' on database ' + @dbName + ''' ' +
			'PRINT ''---------------------------------------------------------------------------'' ' +
			'DECLARE @membername' + cast(@dbcounter as nvarchar(5)) + ' AS nvarchar(50) ' +
			'CREATE TABLE #tmpImpervarolemem (membername nvarchar(256) NOT NULL) ' +
			'INSERT INTO #tmpImpervarolemem ' +
			'SELECT distinct grantee.name ' + 
			'FROM sys.database_role_members ro, ' + 
			'sys.database_principals db_role, ' +
			'sys.database_principals grantee ' + 
			'WHERE	ro.role_principal_id = db_role.principal_id ' +
			'and ro.member_principal_id = grantee.principal_id ' +
			'and db_role.name = ''imperva_role'' '

	-- =====================================================================
	--  Drop the Role Members If they exist
	-- =====================================================================
		SET @executeString = @executeString + 
			'IF EXISTS (SELECT 1 FROM #tmpImpervarolemem) ' +
			'BEGIN ' +
				'PRINT ''==> Dropping the imperva_role role members on: ' + @dbName + ''' ' +
				'DECLARE DropCursor CURSOR FOR SELECT membername from #tmpImpervarolemem ' +
				'OPEN DropCursor ' +
				'FETCH DropCursor INTO @membername' + cast(@dbcounter as nvarchar(5)) + ' ' +
				'WHILE @@Fetch_Status = 0 ' +
				'BEGIN ' +
					'PRINT ''==> Dropping member: '' + @membername' + cast(@dbcounter as nvarchar(5)) + ' ' +
					'SET @membername' + cast(@dbcounter as nvarchar(5)) + ' ' + ' = replace(@membername' + cast(@dbcounter as nvarchar(5)) + ', '''''''','''''''''''') ' +       
					'EXEC(''EXEC sp_droprolemember ''''imperva_role'''', '''''' + @memberName' + cast(@dbcounter as nvarchar(5))  + ' + '''''';'') ' +
					'FETCH DropCursor INTO @membername' + cast(@dbcounter as nvarchar(5)) + ' ' +
				'END ' +
				'CLOSE DropCursor ' +
				'DEALLOCATE DropCursor ' +
			'END '

	-- =====================================================================
	-- Drop the imperva_schema schema if it exists
	-- =====================================================================
		SET @executeString = @executeString + 
			'IF EXISTS(SELECT 1 FROM sys.schemas where name = ''imperva_schema'') ' +
				'BEGIN ' +
					'PRINT ''==> Dropping the imperva_schema schema on: ' + @dbName + ''' ' +
					'DROP SCHEMA [imperva_schema] ' +
				'END '

	-- =====================================================================
	-- Drop the imperva_role role if it exists
	-- =====================================================================
		SET @executeString = @executeString + 
			'IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''imperva_role'' and type = ''R'') ' +
			'BEGIN ' +
				'PRINT ''==> Dropping the imperva_role role on: ' + @dbName + ''' ' +
				'DROP ROLE [imperva_role] ' +
			'END '

	-- =====================================================================
	-- Create the imperva_role role
	-- =====================================================================
		SET @executeString = @executeString + 
			 'PRINT ''==> Creating the imperva_role role on: ' + @dbName + ''' ' +
			 'CREATE ROLE [imperva_role] '

	-- =====================================================================
	-- Re-add the dropped members
	-- =====================================================================
		SET @executeString = @executeString + 
			'IF EXISTS (SELECT 1 FROM #tmpImpervarolemem) ' +
			'BEGIN ' +
				'PRINT ''==> Re-adding the imperva_role role members on: ' + @dbName + ''' ' +
				'DECLARE DropCursor CURSOR FOR SELECT membername from #tmpImpervarolemem ' +
				'OPEN DropCursor ' +
				'FETCH DropCursor INTO @membername' + cast(@dbcounter as nvarchar(5)) + ' ' +
				'WHILE @@Fetch_Status = 0 ' +
					'BEGIN ' +
						'PRINT ''==> Re-adding member: '' + @membername' + cast(@dbcounter as nvarchar(5)) + ' ' +
						'SET @membername' + cast(@dbcounter as nvarchar(5)) + ' ' + ' = replace(@membername' + cast(@dbcounter as nvarchar(5)) + ', '''''''','''''''''''') ' +       
						'EXEC(''EXEC sp_addrolemember ''''imperva_role'''', '''''' + @membername' + cast(@dbcounter as nvarchar(5))  + ' + '''''';'') ' +
						'FETCH DropCursor INTO @membername' + cast(@dbcounter as nvarchar(5)) + ' ' +
					'END ' +
			   'CLOSE DropCursor ' +
			   'DEALLOCATE DropCursor ' +
			 'END ' +
			 'PRINT ''---------------------------------------------------------------------------'' ' +
			 'PRINT ''END ==> Database role creation for MSSQL version ' + @dbVer + ' on database ' + @dbName + ''' ' +
			 'PRINT ''---------------------------------------------------------------------------'' '

	-- =====================================================================
	-- Required Permissions on Any Database on MSSQL 2008 and Higher
	-- =====================================================================
	-- The following permissions are required on any database:

		SET @executeString2 = @executeString2 +
			'PRINT ''---------------------------------------------------------------------------'' ' +
			'PRINT ''START ==> Granting select & execute privilages on database: ' + @dbName  + ''' ' +
			'PRINT ''---------------------------------------------------------------------------'' ' +
			'PRINT ''==> ASSESSMENT - Granting common SELECT privileges'' ' +
			'GRANT SELECT ON dbo.sysobjects					TO imperva_role ' +
			'GRANT SELECT ON dbo.sysprotects				TO imperva_role ' +
			'GRANT SELECT ON dbo.sysusers					TO imperva_role ' +
			'GRANT SELECT ON sys.asymmetric_keys			TO imperva_role ' +
			'GRANT SELECT ON sys.database_files				TO imperva_role ' +
			'GRANT SELECT ON sys.database_permissions		TO imperva_role ' +
			'GRANT SELECT ON sys.database_principals		TO imperva_role ' +
			'GRANT SELECT ON sys.database_role_members		TO imperva_role ' +
			'GRANT SELECT ON sys.key_encryptions			TO imperva_role ' +
			'GRANT SELECT ON sys.objects					TO imperva_role ' +
			'GRANT SELECT ON sys.procedures					TO imperva_role ' +
			'GRANT SELECT ON sys.schemas					TO imperva_role ' +
			'GRANT SELECT ON sys.sql_modules				TO imperva_role ' +
			'GRANT SELECT ON sys.symmetric_keys				TO imperva_role ' +
			'GRANT SELECT ON sys.system_objects				TO imperva_role '

		SET @executeString2 = @executeString2 +
			'PRINT ''==> STORED PROCEDURE ANALYSIS - Granting SELECT & EXECUTE privileges'' ' +
			'GRANT SHOWPLAN									TO imperva_role ' +
			'GRANT EXECUTE									TO imperva_role ' +
			'GRANT SELECT ON dbo.sysobjects					TO imperva_role ' +
			'GRANT SELECT ON dbo.syscomments				TO imperva_role ' +
			'PRINT ''---------------------------------------------------------------------------'' ' +
			'PRINT ''END ==> Granting select & execute privilages on database: ' + @dbName  + ''' ' +
			'PRINT ''---------------------------------------------------------------------------'' '

		EXECUTE(@executeString + @executeString2)
		FETCH DBCursor INTO @dbName
	END
	CLOSE DBCursor
	DEALLOCATE DBCursor

	PRINT '---------------------------------------------------------------------------'
	PRINT 'START ==> Adding ' + @Imperva_user + ' to all the databases ' + @dbName
	PRINT '---------------------------------------------------------------------------'

-- =====================================================================
-- Adding @Imperva_user to all the database and imperva_role
-- =====================================================================
	USE master
	SET @dbName = ''
	SET @executeString = ''

	DECLARE DBCursor CURSOR FOR 
	SELECT name FROM sys.databases WHERE 
	user_access <>  1 AND		--not single user
	state = 0 AND				--online
	is_read_only = 0			--not read only

	OPEN DBCursor
	FETCH NEXT FROM DBCursor INTO @dbName

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @currentDB =  @dbName
		SET @dbName = quotename(replace(@dbName, '''', ''''''))
		SET @executeString = ''

		SET @executeString = @executeString +
		'USE ' + quotename(@currentDB) + ' ' +
		-- =====================================================================
		-- Check if the login already has access to this database
		-- =====================================================================
		'IF EXISTS (select * FROM sys.database_principals WHERE name = ''' + @Imperva_user_esc + ''' and type in (''S'',''U'',''G'')) ' +
		'BEGIN ' +
			-- =================================================================
			-- Check if login already part of imperva_role role
			-- =================================================================
			'IF NOT EXISTS (SELECT grantee.name ' + 
			'FROM sys.database_role_members ro, ' +
			'sys.database_principals db_role, ' + 
			'sys.database_principals grantee ' +
			'WHERE ro.role_principal_id = db_role.principal_id ' +
			'AND ro.member_principal_id = grantee.principal_id ' +
			'AND db_role.name = ''imperva_role'' ' +
			'AND grantee.name = ''' + @Imperva_user_esc + ''') ' +
			'BEGIN ' +
				'PRINT ''==> Granting imperva_role role to ' + @Imperva_user_esc + ' on database ' + @dbName + ''' ' +
				'EXECUTE sp_addrolemember ''imperva_role''' + ', [' + @Imperva_user + '] ' +
			'END ' +
		'END ' +
		-- =====================================================================
		-- If the login does not have access to this database
		-- =====================================================================
		'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''' + @Imperva_user_esc + ''' AND type IN (''S'',''U'',''G'')) ' +
		'BEGIN ' +
			--'CREATE SCHEMA [imperva_schema]' +
			'CREATE USER [' + @Imperva_user + '] ' + ' FOR LOGIN [' + @Imperva_user + '] ' + 
			'PRINT ''==> Granting imperva_role role to ' + @Imperva_user_esc + ' on database '  + @dbName + ''' ' +
			'EXECUTE sp_addrolemember ''imperva_role''' + ', [' + @Imperva_user + '] ' +
		'END '

		EXECUTE(@executeString)
		FETCH DBCursor INTO @dbName
	END
	CLOSE DBCursor
	DEALLOCATE DBCursor


--> =========================================================
--> new added by Guido Mark / Dun & Bradstreet Switzerland
		EXECUTE ('Deny Execute to [' + @Imperva_user + ']' )
--> =========================================================

-- =====================================================================
-- Grant server permission to login for sql server 2005/2008
-- =====================================================================

	IF (@dbVerInt <11 )  
	BEGIN
		PRINT '==> Granting catalog privileges to: ''' + @Imperva_user + ''''
		EXECUTE ('grant VIEW ANY DATABASE to [' + @Imperva_user + ']' )
		EXECUTE ('grant VIEW ANY DEFINITION to [' + @Imperva_user + ']' )
		EXECUTE ('grant VIEW SERVER STATE TO [' + @Imperva_user + ']' )

	END
	ELSE
-- =====================================================================
-- Grant server permission to user defined server role on >= sql 2012
-- =====================================================================
	BEGIN
		PRINT '==> Starting MSSql ' + @dbVer + ' user defined server role creation  ' 
		DECLARE  @srvrolemember TABLE (membername nvarchar(256) NOT NULL)
		INSERT INTO @srvrolemember
		SELECT DISTINCT	m.name 
		FROM sys.server_role_members r 
		INNER JOIN sys.server_principals p
		ON r.role_principal_id = p.principal_id 
		INNER JOIN sys.server_principals m
		ON r.member_principal_id = m.principal_id
		WHERE p.name = 'imperva_server_role'
-- =====================================================================
-- Drop the imperva_server_role server role members, if exists
-- =====================================================================
		IF EXISTS (SELECT 1 FROM @srvrolemember)
		BEGIN
		  PRINT '==> Dropping the imperva_server_role server role members'
		  DECLARE DropCursor CURSOR FOR SELECT membername FROM @srvrolemember
		  
		  OPEN DropCursor
		  
		  FETCH DropCursor INTO @memberName
		  WHILE @@Fetch_Status = 0
		  BEGIN
			SET @memberName = replace(@memberName,'''','''''')
			PRINT '==> Dropping member: ''' + @memberName + ''''
			EXEC ('ALTER SERVER ROLE [imperva_server_role] DROP MEMBER [' + @memberName + '] ;')
			FETCH DropCursor INTO @memberName
		  END

		  CLOSE DropCursor
		  DEALLOCATE DropCursor
		END

		IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'imperva_server_role' and type = 'R')
		BEGIN
		  PRINT '==> Dropping the user defined server role imperva_server_role'
		  --must use dynamic query as sql 2005 will treat it as syntax error
		  exec('DROP SERVER ROLE [imperva_server_role]')
		END

-- =====================================================================
-- Create imperva_server_role server role members
-- =====================================================================
		PRINT '==> Creating the user defined server role imperva_server_role '
		exec('CREATE SERVER ROLE [imperva_server_role]')

-- =====================================================================
-- Grant SELECT privilages to the the imperva_server_role
-- =====================================================================
		PRINT '==> Granting Server role imperva_server_role'
		GRANT VIEW ANY DATABASE TO imperva_server_role  
		GRANT VIEW ANY DEFINITION TO imperva_server_role	
		GRANT VIEW SERVER STATE TO imperva_server_role


-- =====================================================================
-- Re-add the dropped members
-- =====================================================================
		IF EXISTS (SELECT 1 FROM @srvrolemember)
		BEGIN
			PRINT '==> Re-adding the server role members'
			DECLARE DropCursor CURSOR FOR SELECT membername from @srvrolemember
			OPEN DropCursor
			FETCH DropCursor INTO @memberName
			WHILE @@Fetch_Status = 0
			BEGIN
				SET @memberName = replace(@memberName,'''','''''')
				PRINT '==> Re-adding member: ''' + @memberName + ''''
				EXEC('ALTER SERVER ROLE [imperva_server_role] ADD MEMBER [' + @memberName + '] ;')
				FETCH DropCursor INTO @memberName
			END
			CLOSE DropCursor
			DEALLOCATE DropCursor
		END

-- =====================================================================
-- Add server role member @Guardium_user if not exists
-- =====================================================================
		IF NOT EXISTS (SELECT 1 FROM @srvrolemember WHERE membername=@Imperva_user )
		BEGIN
				 PRINT '==> add server role member : ''' + @Imperva_user + ''''
				 exec('ALTER SERVER ROLE [imperva_server_role] ADD MEMBER [' + @Imperva_user + '] ;')
		END
	END
	PRINT '<<<==================================================================<<<'
	PRINT '<<< COMPLETED - Add and Grant imperva_role role to: ''' + @Imperva_user + ' on all databases.'
	PRINT '<<<==================================================================<<<'
	PRINT ''
END