DECLARE @SQL nvarchar(4000)
DECLARE @message nvarchar(255)
DECLARE @name nvarchar(255)
DECLARE @dbContext nvarchar(256)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name LIKE 'E%'
ORDER BY name
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = 'Database: ' + @name
PRINT @message
SET @SQL = 'USE ' + @name + CHAR(13) + '
IF EXISTS (SELECT * FROM ' + @name + '.sys.schemas WHERE name = ''Knjigovodstvo'')
DROP SCHEMA [Knjigovodstvo]'
print @SQL
exec sp_executesql @SQL
SET @SQL = 'USE ' + @name + CHAR(13) + '
IF EXISTS (SELECT * FROM ' + @name + '.sys.database_principals WHERE name = ''Knjigovodstvo'')
DROP USER [Knjigovodstvo]'
print @SQL
exec sp_executesql @SQL
SET @SQL = 'USE ' + @name + CHAR(13) + 'CREATE USER [Knjigovodstvo] FOR LOGIN [Knjigovodstvo]'
PRINT @SQL
SET @dbContext = @name + '.dbo.' + 'sp_executeSQL'
EXEC @dbContext @SQL
SET @SQL = 'USE ' + @name + CHAR(13) + 'EXEC sp_addrolemember N''db_datareader'', N''Knjigovodstvo'''
PRINT @SQL
SET @dbContext = @name + '.dbo.' + 'sp_executeSQL'
EXEC @dbContext @SQL
SET @SQL = 'USE ' + @name + CHAR(13) + 'GRANT EXECUTE ON '+ @name+ '.[dbo].[db_ComputersOnLan_GetParameters] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+ '.[dbo].[db_Customers_GetById] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_Customers_ListAll] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_GalenicJournal_ListSpentComponents] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_HzzoOrthopedicList_GetByCode] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_HzzoOrthopedicList_GetById] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_HzzoOrthopedicList_GetCount] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_HzzoOrthopedicList_ListAll] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_HzzoOrthopedicList_ListRollupByCode] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_Income_ListByPeriod] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_Items_ListByOnhandChange] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_Items_ListMaterialCardsOutOfSync] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_MagistralJournal_ListSpentComponents] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_Narcotic_GetCard] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_Narcotic_GetCardBuilded] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_Orthopedic_ListByPatient] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_Orthopedic_ListBySecurityNo] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_Parameters_GetParameter] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_Parameters_SetParameter] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_Prescription_ListByPatient] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_Prescription_ListBySecurityNo] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_Prescription_ListForReissue] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_Prescription_ListInvoicedByPeriod] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_Prescription_ListReissuedByPeriod] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_Sale_ListOrthopedics] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_StockReport_ListNoOutput] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_UserParameters_GetUserParameter] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_Vat_GetById] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_Vat_ListActive] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_Vat_ListAll] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_Vat_ListNotInActiveVat] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_Vendors_GetById] TO Knjigovodstvo ' + CHAR(10)
SET @SQL = @SQL + 'GRANT EXECUTE ON '+ @name+'.[dbo].[db_Vendors_ListAll] TO Knjigovodstvo ' + CHAR(10)
PRINT @SQL
EXEC sp_executesql @SQL
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor