SQL script gecombineeerd
IF (SELECT COUNT(*) FROM sys.database_principals WHERE name = 'udr_db_FocusOneUserRole' AND type = 'R') = 0
BEGIN
CREATE ROLE [udr_db_FocusOneUserRole]
END
ELSE
BEGIN
PRINT 'User Defined Role "udr_db_FocusOneUserRole" is (already) created.'
END
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)
END
ELSE
BEGIN
PRINT 'Windows Group "FocusOneExcelGroup" is (already) created.'
END
-- Fist check if the table exists or no tand then add it
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='dbo.Items')
BEGIN GRANT SELECT ON dbo.Items TO udr_db_FocusOneUserRole;END
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='dbo.ItemClasses')
BEGIN GRANT SELECT ON dbo.ItemClasses TO udr_db_FocusOneUserRole;END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='dbo.ItemAccounts')
BEGIN GRANT SELECT ON dbo.ItemAccounts TO udr_db_FocusOneUserRole;END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='dbo.cicmpy')
BEGIN GRANT SELECT ON dbo.cicmpy TO udr_db_FocusOneUserRole;END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='dbo.cicntp')
BEGIN GRANT SELECT ON dbo.cicntp TO udr_db_FocusOneUserRole;END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='dbo.Land')
BEGIN GRANT SELECT ON dbo.Land TO udr_db_FocusOneUserRole;END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='dbo.StockBalances')
BEGIN GRANT SELECT ON dbo.StockBalances TO udr_db_FocusOneUserRole;END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='dbo.pred')
BEGIN GRANT SELECT ON dbo.pred TO udr_db_FocusOneUserRole;END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='dbo.betcd')
BEGIN GRANT SELECT ON dbo.betcd TO udr_db_FocusOneUserRole;END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='dbo.btwtrs')
BEGIN GRANT SELECT ON dbo.btwtrs TO udr_db_FocusOneUserRole;END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='dbo.ordlev')
BEGIN GRANT SELECT ON dbo.ordlev TO udr_db_FocusOneUserRole;END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='dbo.Valuta')
BEGIN GRANT SELECT ON dbo.Valuta TO udr_db_FocusOneUserRole;END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='dbo.rates')
BEGIN GRANT SELECT ON dbo.rates TO udr_db_FocusOneUserRole;END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='dbo.staffl')
BEGIN GRANT SELECT ON dbo.staffl TO udr_db_FocusOneUserRole;END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='dbo.Stfoms')
BEGIN GRANT SELECT ON dbo.Stfoms TO udr_db_FocusOneUserRole;END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='dbo.magaz')
BEGIN GRANT SELECT ON dbo.magaz TO udr_db_FocusOneUserRole;END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='dbo.bedryf')
BEGIN GRANT SELECT ON dbo.bedryf TO udr_db_FocusOneUserRole;END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='dbo.humres')
BEGIN GRANT SELECT ON dbo.humres TO udr_db_FocusOneUserRole;END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='dbo.pwruc')
BEGIN GRANT SELECT ON dbo.pwruc TO udr_db_FocusOneUserRole;END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='dbo.pwrole')
BEGIN GRANT SELECT ON dbo.pwrole TO udr_db_FocusOneUserRole;END
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]
END
ELSE
BEGIN
PRINT 'Windows Group "FocusOneExcelGroup" (already) created.'
END
IF (SELECT COUNT(*) FROM sys.database_principals WHERE name like '%FocusOneExcelUser') <> 1
BEGIN
CREATE USER [FocusOneExcelUser] FOR LOGIN [<<DOMAIN_NAME>>\FocusOneExcelGroup]
END
ELSE
BEGIN
PRINT 'Login "FocusOneExcelUser" is (already) created.'
END