SQL script gecombineeerd
-- Step 1 Manually create NT USER GROUP [@DOMAIN_NAME\FocusOneExcelGroup] on PC or Server
-- Add actual user to the NT User Group [@DOMAIN_NAME\FocusOneExcelGroup]
-- Set Exact Database
USE [002]
-- Set @DOMAIN_NAME to actual Domain Name:
DECLARE @DOMAIN_NAME AS VARCHAR(10) = 'SCHENKER'
-- Step 2 Create user [FocusOneExcelUser]
-- Step 3 Create Role [udr_db_FocusOneUserRole]
-- Step 4 Map database user [FocusOneExcelUser] to role [udr_db_FocusOneUserRole]
-- Step 5 Create SQL Login for [@DOMAIN_NAME\FocusOneExcelGroup]
-- Step 6 Grant SELECT rights TO [udr_db_FocusOneUserRole] for 20 Exact Tables
-- Step 1 Manually create NT USER GROUP [@DOMAIN_NAME\FocusOneExcelGroup] on PC or Server
-- See manual on https://focusone.nl/fom/qp_4.0/index.htm Specific https://focusone.nl/fom/qp_4.0/focusone_sql_configurator__.htm
-- Step 2 Create user [FocusOneExcelUser]
IF (SELECT COUNT(*) FROM sys.database_principals WHERE name like '%FocusOneExcelUser') <> 1
BEGIN
CREATE USER [FocusOneExcelUser] FOR LOGIN [@DOMAIN_NAME\FocusOneExcelGroup]
PRINT 'Step 2 User [FocusOneExcelUser] created.'
END
ELSE
BEGIN
PRINT 'Step 2 User [FocusOneExcelUser] already exists.'
END
-- Step 3 Create Role [udr_db_FocusOneUserRole]
IF (SELECT COUNT(*) FROM sys.database_principals WHERE name = 'udr_db_FocusOneUserRole' AND type = 'R') = 0
BEGIN
CREATE ROLE [udr_db_FocusOneUserRole]
PRINT 'Step 2 User Defined Role [udr_db_FocusOneUserRole] created.'
END
ELSE
BEGIN
PRINT 'Step 2 User Defined Role [udr_db_FocusOneUserRole] already exists.'
END
-- Step 4 Map database user [FocusOneExcelUser] to role [udr_db_FocusOneUserRole] if user exists
IF (SELECT COUNT(*) FROM sys.database_principals WHERE name like '%FocusOneExcelUser') = 1
BEGIN
EXEC sp_addrolemember udr_db_FocusOneUserRole, FocusOneExcelUser
PRINT 'Step 4 Database User [FocusOneExcelUser] Mapped to [udr_db_FocusOneUserRole].'
END
ELSE
BEGIN
PRINT 'Step 4 Database User [FocusOneExcelUser] NOT Mapped to [udr_db_FocusOneUserRole].'
END
-- Step 5 Create SQL Login for [@DOMAIN_NAME\FocusOneExcelGroup]
IF (SELECT COUNT(*) FROM master.sys.syslogins WHERE name like '%FocusOneExcelGroup' AND isntgroup = '1') = 0
BEGIN
CREATE LOGIN [@DOMAIN_NAME\FocusOneExcelGroup] FROM WINDOWS WITH DEFAULT_DATABASE=[master] --(Attach the Windows NT Group in SQL Security section)
PRINT 'Step 5 Login created for [FocusOneExcelGroup].'
END
ELSE
BEGIN
PRINT 'Step 5 Login [FocusOneExcelGroup] already exists.'
END
-- Step 6 Grant SELECT rights TO [udr_db_FocusOneUserRole] for 20 Exact Tables
-- Fist check if the table exists and then add it
IF (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='Items') =1
BEGIN
GRANT SELECT ON dbo.Items TO [udr_db_FocusOneUserRole]
PRINT 'Step 6 01. SELECT Granted On Table Table dbo.Items in UDR [udr_db_FocusOneUserRole].'
END
ELSE
BEGIN
PRINT 'Step 6 01. ERROR: SELECT NOT Granted On Table Table dbo.Items in UDR [udr_db_FocusOneUserRole] or Table does not exist.'
END
IF (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='ItemClasses') =1
BEGIN
GRANT SELECT ON dbo.ItemClasses TO [udr_db_FocusOneUserRole]
PRINT 'Step 6 02. SELECT Granted On Table Table dbo.ItemClasses in UDR [udr_db_FocusOneUserRole].'
END
ELSE
BEGIN
PRINT 'Step 6 02. ERROR: SELECT NOT Granted On Table dbo.ItemClasses in UDR [udr_db_FocusOneUserRole] or Table does not exist.'
END
IF (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='ItemAccounts') =1
BEGIN
GRANT SELECT ON dbo.ItemAccounts TO [udr_db_FocusOneUserRole]
PRINT 'Step 6 03. SELECT Granted On Table dbo.ItemAccounts in UDR [udr_db_FocusOneUserRole].'
END
ELSE
BEGIN
PRINT 'Step 6 03. ERROR: SELECT NOT Granted On Table dbo.ItemAccounts in UDR [udr_db_FocusOneUserRole] or Table does not exist.'
END
IF (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='cicmpy') =1
BEGIN
GRANT SELECT ON dbo.cicmpy TO [udr_db_FocusOneUserRole]
PRINT 'Step 6 04. SELECT Granted On Table dbo.cicmpy in UDR [udr_db_FocusOneUserRole].'
END
ELSE
BEGIN
PRINT 'Step 6 04. ERROR: SELECT NOT Granted On Table dbo.cicmpy in UDR [udr_db_FocusOneUserRole] or Table does not exist.'
END
IF (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='cicntp') =1
BEGIN
GRANT SELECT ON dbo.cicntp TO [udr_db_FocusOneUserRole]
PRINT 'Step 6 05. SELECT Granted On Table dbo.cicntp in UDR [udr_db_FocusOneUserRole].'
END
ELSE
BEGIN
PRINT 'Step 6 05. ERROR: SELECT NOT Granted On Table dbo.cicntp in UDR [udr_db_FocusOneUserRole] or Table does not exist.'
END
IF (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='Land') =1
BEGIN
GRANT SELECT ON dbo.Land TO [udr_db_FocusOneUserRole]
PRINT 'Step 6 06. SELECT Granted On Table dbo.Land in UDR [udr_db_FocusOneUserRole].'
END
ELSE
BEGIN
PRINT 'Step 6 06. ERROR: SELECT NOT Granted On Table dbo.Land in UDR [udr_db_FocusOneUserRole] or Table does not exist.'
END
IF (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='StockBalances') =1
BEGIN
GRANT SELECT ON dbo.StockBalances TO [udr_db_FocusOneUserRole]
PRINT 'Step 6 07. SELECT Granted On Table dbo.StockBalances in UDR [udr_db_FocusOneUserRole].'
END
ELSE
BEGIN
PRINT 'Step 6 07. ERROR: SELECT NOT Granted On Table dbo.StockBalances in UDR [udr_db_FocusOneUserRole] or the Table does not exist.'
END
IF (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='pred') =1
BEGIN
GRANT SELECT ON dbo.pred TO [udr_db_FocusOneUserRole]
PRINT 'Step 6 08. SELECT Granted On Table dbo.pred in UDR [udr_db_FocusOneUserRole].'
END
ELSE
BEGIN
PRINT 'Step 6 08. ERROR: SELECT NOT Granted On Table dbo.pred in UDR [udr_db_FocusOneUserRole] or Table does not exist.'
END
IF (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='betcd') =1
BEGIN
GRANT SELECT ON dbo.betcd TO [udr_db_FocusOneUserRole]
PRINT 'Step 6 09. SELECT Granted On Table dbo.betcd in UDR [udr_db_FocusOneUserRole].'
END
ELSE
BEGIN
PRINT 'Step 6 09. ERROR: SELECT NOT Granted On Table dbo.betcd in UDR [udr_db_FocusOneUserRole] or Table does not exist.'
END
IF (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='btwtrs') =1
BEGIN
GRANT SELECT ON dbo.btwtrs TO [udr_db_FocusOneUserRole]
PRINT 'Step 6 10. SELECT Granted On Table dbo.btwtrs in UDR [udr_db_FocusOneUserRole].'
END
ELSE
BEGIN
PRINT 'Step 6 10. ERROR: SELECT NOT Granted On Table dbo.btwtrs in UDR [udr_db_FocusOneUserRole] or Table does not exist.'
END
IF (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='ordlev') =1
BEGIN
GRANT SELECT ON dbo.ordlev TO [udr_db_FocusOneUserRole]
PRINT 'Step 6 11. SELECT Granted On Table dbo.ordlev in UDR [udr_db_FocusOneUserRole].'
END
ELSE
BEGIN
PRINT 'Step 6 11. ERROR: SELECT NOT Granted On Table dbo.ordlev in UDR [udr_db_FocusOneUserRole] or Table does not exist.'
END
IF (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='Valuta') =1
BEGIN
GRANT SELECT ON dbo.Valuta TO [udr_db_FocusOneUserRole]
PRINT 'Step 6 12. SELECT Granted On Table dbo.Valuta in UDR [udr_db_FocusOneUserRole].'
END
ELSE
BEGIN
PRINT 'Step 6 12. ERROR: SELECT NOT Granted On Table dbo.Valuta in UDR [udr_db_FocusOneUserRole] or Table does not exist.'
END
IF (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='rates') =1
BEGIN
GRANT SELECT ON dbo.rates TO [udr_db_FocusOneUserRole]
PRINT 'Step 6 13. SELECT Granted On Table dbo.rates in UDR [udr_db_FocusOneUserRole].'
END
ELSE
BEGIN
PRINT 'Step 6 13. ERROR: SELECT NOT Granted On Table dbo.rates in UDR [udr_db_FocusOneUserRole] or Table does not exist.'
END
IF (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='staffl') =1
BEGIN
GRANT SELECT ON dbo.staffl TO [udr_db_FocusOneUserRole]
PRINT 'Step 6 14. SELECT Granted On Table dbo.staffl in UDR [udr_db_FocusOneUserRole].'
END
ELSE
BEGIN
PRINT 'Step 6 14. ERROR: SELECT NOT Granted On Table dbo.staffl in UDR [udr_db_FocusOneUserRole] or Table does not exist.'
END
IF (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='Stfoms') =1
BEGIN
GRANT SELECT ON dbo.Stfoms TO [udr_db_FocusOneUserRole]
PRINT 'Step 6 15. SELECT Granted On Table dbo.Stfoms in UDR [udr_db_FocusOneUserRole].'
END
ELSE
BEGIN
PRINT 'Step 6 15. ERROR: SELECT NOT Granted On Table dbo.Stfoms in UDR [udr_db_FocusOneUserRole] or Table does not exist.'
END
IF (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='magaz') =1
BEGIN
GRANT SELECT ON dbo.magaz TO [udr_db_FocusOneUserRole]
PRINT 'Step 6 16. SELECT Granted On Table dbo.magaz in UDR [udr_db_FocusOneUserRole].'
END
ELSE
BEGIN
PRINT 'Step 6 16. ERROR: SELECT NOT Granted On Table dbo.magaz in UDR [udr_db_FocusOneUserRole] or Table does not exist.'
END
IF (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='bedryf') =1
BEGIN
GRANT SELECT ON dbo.bedryf TO [udr_db_FocusOneUserRole]
PRINT 'Step 6 17. SELECT Granted On Table dbo.bedryf in UDR [udr_db_FocusOneUserRole].'
END
ELSE
BEGIN
PRINT 'Step 6 17. ERROR: SELECT NOT Granted On Table dbo.bedryf in UDR [udr_db_FocusOneUserRole] or Table does not exist.'
END
IF (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='humres') =1
BEGIN
GRANT SELECT ON dbo.humres TO [udr_db_FocusOneUserRole]
PRINT 'Step 6 18. SELECT Granted On Table dbo.humres in UDR [udr_db_FocusOneUserRole].'
END
ELSE
BEGIN
PRINT 'Step 6 18. ERROR: SELECT NOT Granted On Table dbo.humres in UDR [udr_db_FocusOneUserRole] or Table does not exist.'
END
IF (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='pwruc') =1
BEGIN
GRANT SELECT ON dbo.pwruc TO [udr_db_FocusOneUserRole]
PRINT 'Step 6 19. SELECT Granted On Table dbo.pwruc in UDR [udr_db_FocusOneUserRole].'
END
ELSE
BEGIN
PRINT 'Step 6 19. ERROR: SELECT NOT Granted On Table dbo.pwruc in UDR [udr_db_FocusOneUserRole] or Table does not exist.'
END
IF (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='pwrole') =1
BEGIN
GRANT SELECT ON dbo.pwrole TO [udr_db_FocusOneUserRole]
PRINT 'Step 6 20. SELECT Granted On Table dbo.pwrole in UDR [udr_db_FocusOneUserRole].'
END
ELSE
BEGIN
PRINT 'Step 6 20. ERROR: SELECT NOT Granted On Table dbo.pwrole in UDR [udr_db_FocusOneUserRole] or Table does not exist.'
END