How to give SQL User access to execute Stored procedures and read/write access to database

Assumptions: stored procedures and tables are created under schema dbo. in case different schema, change the below code accordingly. 

 --Create Login with a strong password

--Run the below code in Master Database

CREATE LOGIN [sanjeebuser] WITH PASSWORD = 'VeryStrongPa55w0rD1!'

--run the below code in your Selected Database, (optional: run in master database as well)

CREATE USER [sanjeebuser]

 FOR LOGIN [sanjeebuser]

 WITH DEFAULT_SCHEMA =  dbo

GO

--Run the below code in your selected Database. please do not run the code in Master database.

CREATE ROLE [dbstoregeprocedureaccess] AUTHORIZATION [dbo]

GRANT EXECUTE,ALTER,DELETE,UPDATE,INSERT ON SCHEMA::dbo TO dbstoregeprocedureaccess;

GRANT CREATE PROCEDURE TO dbstoregeprocedureaccess;

EXEC sp_addrolemember N'db_datareader', N'sanjeebuser'

EXEC sp_addrolemember N'db_datawriter', N'sanjeebuser'

EXEC sp_addrolemember N'dbstoregeprocedureaccess', N'sanjeebuser'

How to create XSD schema from MSSQL Database table

 You'll need Microsoft SQL Server Management Studio to run the below query. 

Select the database where you want to run the query against. 

Open a new query window and run the below command 

    DECLARE @SanjeebXsdSchema xml

SET @SanjeebXsdSchema = (SELECT top 1 * FROM [dbo].[BOM_DW_Extract] FOR XML AUTO, ELEMENTS, XMLSCHEMA('sanjeebXsdSchema'))

SELECT @SanjeebXsdSchema

Result:

<xsd:schema xmlns:schema="sanjeebXsdSchema" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="sanjeebXsdSchema" elementFormDefault="qualified">

  <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />

  <xsd:element name="dbo.BOM_DW_Extract">

    <xsd:complexType>

      <xsd:sequence>

        <xsd:element name="CompanyCode" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="ParentPart" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="ComponentPart" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="ComponentSequence" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="ComponentQuantity" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="Reference" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="20" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="BomType" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="20" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="StartDate" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="EndDate" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="ModifiedDate" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

      </xsd:sequence>

    </xsd:complexType>

  </xsd:element>

</xsd:schema>