Showing posts with label mssql. Show all posts
Showing posts with label mssql. Show all posts

Friday, 14 May 2021

How to check roles for sql users

 

The following query returns the members of the database roles.

SELECT DP1.name AS DatabaseRoleName,   

   isnull (DP2.name, 'No members'AS DatabaseUserName   
 FROM sys.database_role_members AS DRM  
 RIGHT OUTER JOIN sys.database_principals AS DP1  
   ON DRM.role_principal_id = DP1.principal_id  
 LEFT OUTER JOIN sys.database_principals AS DP2  
   ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.type = 'R'
ORDER BY DP1.name;  

Thursday, 25 February 2021

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'


Wednesday, 6 January 2021

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>


Wednesday, 11 November 2020

How To create SSIS user in Azure SQL server

I've assumed you have already configured domain in azure and have created a domain user.

Create Application in app registration  

First of all Create an app registration [myssissqlserver01]


Set up Active directory Admin in sql server

Now Go to the sql server (not the database) and on settings menu, click Active directory Admin



Click Set Admin and add your domain account. (in case you don't have domain configured , Configure a new domain and create a domain user first)

Connect to the database using MS Sql server management studio

Once admin is set , Open MS Sql server management studio and connect to the database.



Since we have added user to active directory admin, the user will have access to master database. So we can choose default database to connect, 


Create a user in ssis and add to a suitable role

Create a new query window selecting SSISDB database

execute the below queries. Note that the user name should be identical to the Application name created in App registration section . [ myssissqlserver01]

create user [myssissqlserver01] from external provider;

exec sp_addrolemember [db_owner],[myssissqlserver01];


Tuesday, 22 September 2020

How to find first weekday in the month in MSSQL


declare @n datetime = dateadd(mm,0,getDate())

   declare @FirstWeekDayOfMonth int

   SELECT @FirstWeekDayOfMonth= CASE

         WHEN Datename(dw, Dateadd(dd, -Datepart(dd, @n) + 1, @n)) = 'Saturday' THEN Day(Dateadd(dd, -Datepart(dd, @n) + 3, @n))

         WHEN Datename(dw, Dateadd(dd, -Datepart(dd, @n) + 1, @n)) = 'Sunday' THEN Day(Dateadd(dd, -Datepart(dd, @n) + 2, @n))

         ELSE Day(Dateadd(dd, -Datepart(dd, @n) + 1, @n))

       END 

   select @FirstWeekDayOfMonth



Note: to find out first day of next month , change  first line to dateadd(mm,1,getDate()) 


1st day of next N month , change the first line to dateadd(mm,n,getDate())

Monday, 17 August 2020

Create Azure SQL User and add a sql role

I wanted to add a new sql user "sqluser" in Azure SQL server to access a database "sanjeeb". 

Steps to create a new SQL user. 

1) Connect to the Azure sql server with existing user using Microsoft SQL Server Management studio

2) Open new query window under Master database


3) Run the below script
    
    CREATE LOGIN [sqllogin] WITH PASSWORD = 'verystrongpassword#1' 
   



4) Open new query window under database "sanjeeb"

5) Run the below script

CREATE USER [sqluser]
FOR LOGIN [sqllogin]
WITH DEFAULT_SCHEMA =  dbo
GO

-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'sqluser'
GO

6) You've now created a user login "sqllogin", user account "sqluser" and added the user to "db_owner role"



7) Connect to the database using the new user login account. 

login: sqllogin
password:verystrongpassword#1


8) Click Options and select Connection properties tab

9) input Connect to database = sanjeeb

10) Click connect

How to check roles for sql users

  The following query returns the members of the database roles. SELECT  DP1.name  AS  DatabaseRoleName,        isnull  (DP2.name,  'No ...