MEET FEATURE REQUIREMENTS, SCHEDULE, and BUDGET! Consulting available to teach your organization to apply this methodology and tools for powerful quantitative management of your business processes.
- Instruction of your staff at your site, using courseware, application example, and a functional Framework.
- Mentoring your key staff on an ongoing or periodic basis, on these tools or on Quantitative Program Management.
- Contracting or Employment in your organization on specific topics.
Check out my YouTube Channel: Power Operational Intelligence
Now Live! Overview, Structure, Task Data, Table Design, SQL Server, and Re-Linking now showing.
Video courses covering material on this website, and more, are presented in the playlists.
Code snippet links at YouTube Code Snippets. Twitter at @poweroperation1, #poweropi, #poweroperationalintelligence.
Subscribe on YouTube, and click the "Notification" Bell icon to be notified as content is published.
Shared SQL Server with Remote ClientsSQL Procedures are shown below for explanation and example. Configurable SQL for all these procedures is included within components of PowerOpI SQLServerRemote. Security NoteThis page supports security for a program suitable for a small management group, using SQL Server Security. Page Database Security provides more general security suitable to larger programs with broader participation and supports several login security schemes including Windows/ActiveDirectory/Domain Controller (AD/DC) as well as SQL Server Security. AD/DC provides much security context to Windows Logins, but you must "manually" set that context in SQL Server, which adds significant complexity. Working Paths in this are very difficult to find, or need significant education on Windows Security. The Working Path provided in the download SQLServerTool uses broad permissions and if it is to be used in an environment that requires real security, enlist an IT Professional to for a security audit and breakdown of the provided security, to recommend tighter permissions in order to provide real security. It's not obvious that security provided by SQLServerTool is less than that of a deployed MS Access or MySQL tool. But it is not as secure as a configuration using Windows Logins with Active Directory Domain Controller. Example SQLFrom SSMS directives for PowerOpI ToolSource, execute Directive to Create Project database
Directive: CREATE_Server1DatabaseName1 /* SQL Server CREATE Database */ /* #VBTEXTJOIN: EXPORTWITH dbo; */ DECLARE @dbname nvarchar(128) SET @dbname = N'SQLServerRemoteDB' IF (NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE ('[' + name + ']' = @dbname OR name = @dbname))) BEGIN CREATE DATABASE SQLServerRemoteDB END; GO USE SQLServerRemoteDB; GO IF EXISTS(SELECT is_trustworthy_on, name FROM sys.databases WHERE name = 'SQLServerRemoteDB' AND is_trustworthy_on=0) ALTER DATABASE SQLServerRemoteDB SET TRUSTWORTHY ON; From SSMS directives for PowerOpI ToolSource, execute Directive to Add SQL Server same-domain logins Directive: qSetPermissions /* Same-Domain Login Set ROLE membership and permissions */ USE SQLServerRemoteDB; GO /* #VBTEXTJOIN: EXPORT; */ /* Create ROLE [Developer] */ IF DATABASE_PRINCIPAL_ID( N'Developer'] ) IS NULL BEGIN CREATE ROLE [Developer] AUTHORIZATION db_securityadmin END; GRANT IMPERSONATE ANY LOGIN ON SCHEMA::dbo TO [Developer]; GO /* Set Role Attributes on each database */ USE SQLServerRemoteDB; GO GRANT CONTROL ON SCHEMA::dbo TO [Developer] WITH GRANT OPTION; /* CONTROL implies ALTER, DELETE, EXECUTE, INSERT, SELECT, UPDATE, VIEW DEFINITION */ GO /* Add MEMBERs to the ROLEs for each database */ ALTER ROLE [Public] ADD MEMBER Owner; ALTER ROLE Developer ADD MEMBER Owner; ALTER ROLE db_owner ADD MEMBER Owner; ALTER ROLE db_ddladmin ADD MEMBER Owner; ALTER ROLE [Public] ADD MEMBER netbo; ALTER ROLE Developer ADD MEMBER netbo; ALTER ROLE db_owner ADD MEMBER netbo; ALTER ROLE db_ddladmin ADD MEMBER netbo; From SSMS directives for PowerOpI ToolSource, execute Directive to Add SQL Server remote log-ins and server-level security
Directive: CREATE_LoginAndUserPermissions_SSMS /* SQL Server Login Set ROLE membership and permissions for each intended Database */ /* #VBTEXTJOIN: EXPORT; */ USE master; GO /* Grant EXECUTE to OLEDB_Provider */ GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO [Public]; GO /* Set Role Attributes on each database */ /* REPEAT indented section for each database to enable intended use*/ USE SQLServerRemoteDB; GO /* Create ROLE [Developer] */ IF DATABASE_PRINCIPAL_ID( N'Developer' ) IS NULL BEGIN CREATE ROLE [Developer] AUTHORIZATION db_securityadmin END; GO /* CONTROL implies ALTER, DELETE, EXECUTE, INSERT, SELECT, UPDATE, VIEW DEFINITION */ GRANT CONTROL ON SCHEMA::dbo TO [Developer] WITH GRANT OPTION; GO /* Add MEMBERs to the ROLEs for each database */ /* SET MEMBER name and REPEAT indented section for each intended Database, */ /* for each intended Database User, to enable intended use */ ALTER ROLE Developer ADD MEMBER ProgramManagement; ALTER ROLE db_datareader ADD MEMBER ProgramManagement; ALTER ROLE db_datawriter ADD MEMBER ProgramManagement; ALTER ROLE db_ddladmin ADD MEMBER ProgramManagement; --ALTER ROLE db_executor ADD MEMBER ProgramManagement; -- in CONTROL permission granted above. /* FOR USERS TO BE GRANTED SPECIAL PRIVILEGES */ ALTER ROLE db_owner ADD MEMBER ProgramManagement; ALTER ROLE db_securityadmin ADD MEMBER ProgramManagement; GO ALTER ROLE Developer ADD MEMBER ProgramManagement2; ALTER ROLE db_datareader ADD MEMBER ProgramManagement2; ALTER ROLE db_datawriter ADD MEMBER ProgramManagement2; ALTER ROLE db_ddladmin ADD MEMBER ProgramManagement2; --ALTER ROLE db_executor ADD MEMBER ProgramManagement2; -- in CONTROL permission granted above. /* FOR USERS TO BE GRANTED SPECIAL PRIVILEGES */ ALTER ROLE db_owner ADD MEMBER ProgramManagement2; ALTER ROLE db_securityadmin ADD MEMBER ProgramManagement2; GO From SSMS directives for PowerOpI ToolSource, execute Directive to Add SQL Server remote log-ins database-level security
Directive: CREATE_LoginAndUser_SSMS /* SQL Server CREATE Instance LOGINs and Database USERs for each intended Database */ /* #VBTEXTJOIN: EXPORT; */ IF NOT EXISTS (SELECT [sys].[sql_logins].[name], [sys].[sql_logins].[type] FROM [sys].[sql_logins] WHERE [type] IN (N'U', N'S') AND [name] = 'ProgramManagement') BEGIN CREATE LOGIN [ProgramManagement] WITH PASSWORD = 'abcd%12'; --GRANT IMPERSONATE ANY LOGIN ON SCHEMA::dbo TO [Developer]; END IF NOT EXISTS (SELECT [sys].[sql_logins].[name], [sys].[sql_logins].[type] FROM [sys].[sql_logins] WHERE [type] IN (N'U', N'S') AND [name] = 'ProgramManagement2') BEGIN CREATE LOGIN [ProgramManagement2] WITH PASSWORD = 'abcd%12'; --GRANT IMPERSONATE ANY LOGIN ON SCHEMA::dbo TO [Developer]; END GO /* REPEAT section for each database intended */ USE SQLServerRemoteDB; GO /* REPEAT section for each User for which access is intended to the intended Database */ BEGIN IF NOT EXISTS (SELECT [sys].[database_principals].[name], [sys].[database_principals].[type] FROM [sys].[database_principals] WHERE [type] IN (N'U', N'S') AND [name]= 'ProgramManagement') BEGIN CREATE USER [ProgramManagement] FOR LOGIN [ProgramManagement]; END END BEGIN IF NOT EXISTS (SELECT [sys].[database_principals].[name], [sys].[database_principals].[type] FROM [sys].[database_principals] WHERE [type] IN (N'U', N'S') AND [name]= 'ProgramManagement2') BEGIN CREATE USER [ProgramManagement2] FOR LOGIN [ProgramManagement2]; END END GO From SSMS directives for PowerOpI ToolSource, execute Directive to Create support for ACE SPs Directive: CREATE_spConfigACE.sql /* SQL Server DROP and replace spConfigACE */ USE SQLServerRemoteDB; GO /* #VBTEXTJOIN: EXPORTWITH dbo; */ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO DROP PROCEDURE IF EXISTS dbo.spConfigACE; GO CREATE OR ALTER PROCEDURE dbo.spConfigACE WITH EXECUTE AS OWNER AS BEGIN /* Configure OLEDB */ EXEC sp_configure @configname='Show Advanced Options', @configvalue=1; RECONFIGURE WITH OverRide; EXEC sp_configure @configname='Ad Hoc Distributed Queries', @configvalue=1; RECONFIGURE WITH OverRide; EXEC master.sys.sp_MSset_oledb_prop @provider_name=N'Microsoft.ACE.OLEDB.12.0', @property_name=N'AllowInProcess', @property_value=1; EXEC master.sys.sp_MSset_oledb_prop @provider_name=N'Microsoft.ACE.OLEDB.12.0', @property_name=N'DynamicParameters', @property_value=1; From SSMS directives for PowerOpI ToolSource, execute Directives to Create Linked Server(s)
Directive: CREATE_LS_ToolSource.sql Directive: CREATE_LS_P0001_Rollup.sql is similar, replacing "ToolSource" by "P0001_Rollup" and "xlsm" by ".xls". /* SQL Server DROP and Replace LS to ToolSource.xlsm */ USE SQLServerRemoteDB; GO /* #VBTEXTJOIN: EXPORT; */ /* Configure Microsoft.ACE.OLEDB.XX.0 */ EXEC dbo.spConfigACE; /* Drop Linked Server if already exists */ IF EXISTS (SELECT * FROM sys.servers WHERE name = N'LS_SQLServerRemote_ToolSource') BEGIN EXEC sp_dropserver @server = N'LS_SQLServerRemote_ToolSource', @droplogins = 'droplogins'; END /* Create replacement Linked Server */ EXEC dbo.sp_addLinkedServer @server = N'LS_SQLServerRemote_ToolSource', @srvproduct = N'ACE 12.0', @provider = N'Microsoft.ACE.OLEDB.12.0', @datasrc = N'C:\users\netbo\Documents\PowerOpI\SQLServerRemote\ToolSource.xlsm', @location = NULL, @provstr = 'Excel 12.0 Macro;HDR=YES;IMEX=1;', @catalog = NULL; GO /* Create login for remote clients */ /* This login provides access to all callers; website references show how to limit */ EXEC dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LS_SQLServerRemote_ToolSource', @useself= N'FALSE', --@locallogin = N'NULL', -- default is NULL @rmtuser= N'Admin', @rmtpassword = NULL; From SSMS directives for PowerOpI ToolSource, execute Directive to Create Stored Procedures For all CREATE_sp Directives, for example: CREATE_spBuildPlanLS_ToolSource
Directive: CREATE_spBuildPlanLS_ToolSource.sql /* SQL Server CREATE SP using LS SELECT/JOIN ToolSource */ USE SQLServerRemoteDB; GO /* #VBTEXTJOIN: EXPORTWITH dbo; */ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO DROP PROCEDURE IF EXISTS dbo.spBuildPlanLS_SQLServerRemote_ToolSource; GO CREATE OR ALTER PROCEDURE dbo.spBuildPlanLS_SQLServerRemote_ToolSource --WITH EXECUTE AS OWNER AS BEGIN SET NOCOUNT ON; /* Configure Microsoft.ACE.OLEDB.XX.0 */ EXEC dbo.spConfigACE; SELECT dbo.xlAlloc.*, dbo.xlBOM.*, dbo.xlDates.*, dbo.xlBuilds.*, dbo.xlElements.*, dbo.xlPR.*, dbo.xlAlloc.akQty * dbo.xlBOM.bmSubQty AS QtyExt, dbo.xlAlloc.akQty * dbo.xlBOM.bmSubQty * dbo.xlBOM.bmSubCost AS CostExt INTO dbo.xlProto FROM dbo.xlAlloc LEFT OUTER JOIN dbo.xlBOM ON dbo.xlAlloc.akAllocRevKey = dbo.xlBOM.bmBOMItemKeyIn LEFT OUTER JOIN dbo.xlDates ON dbo.xlBOM.bmBOMItemKeyOut = dbo.xlDates.daSchedItemRevKey LEFT OUTER JOIN dbo.xlBuilds ON dbo.xlDates.daSchedItemRevKey = dbo.xlBuilds.bdBuildRevKey LEFT OUTER JOIN dbo.xlElements ON dbo.xlBOM.bmBOMItemKey = dbo.xlElements.emElementItemKey LEFT OUTER JOIN dbo.xlPR ON CAST(dbo.xlAlloc.akPurchReq AS varchar) = CAST(dbo.xlPR.prPurchReq AS varchar); END; |