Shared SQL Server with Remote Clients

SQL Procedures are shown below for explanation and example. Configurable SQL for all these procedures is included within components of PowerOpI SQLServerRemote.

Security Note

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
Note that these open up security quite a bit to support remote log-in with LS and SP support; you may want to tune these security settings to your use.

For example, if external data source content is pushed INTO Tables within the SQL Server database, QUERYs of those Tables would not need security settings needed for LS access to external files. Similarly, if a QUERY result is pushed INTO a database table, QUERYing that Table does not need the security settings allowing access to the external data source tables.

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
Note that these open up security quite a bit to support remote log-in with LS and SP support; you may want to tune these security settings to your use.

For example, if external data source content is pushed INTO Tables within the SQL Server database, QUERYs of those Tables would not need security settings needed for LS access to external files. Similarly, if a QUERY result is pushed INTO a database table, QUERYing that Table does not need the security settings allowing access to the external data source tables.

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