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 22 April 2021

Search by keyword in Stored procedure, Views, Functions And Tables

 CREATE PROCEDURE [util].[SearchAll]       

(@strFind AS VARCHAR(MAX))
AS
BEGIN
    SET NOCOUNT ON; 
    --TO FIND STRING IN ALL PROCEDURES        
    BEGIN
        SELECT OBJECT_NAME(OBJECT_ID) SP_Name
              ,OBJECT_DEFINITION(OBJECT_ID) SP_Definition
        FROM   sys.procedures
        WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@strFind+'%'
    END 

    --TO FIND STRING IN ALL VIEWS        
    BEGIN
        SELECT OBJECT_NAME(OBJECT_ID) View_Name
              ,OBJECT_DEFINITION(OBJECT_ID) View_Definition
        FROM   sys.views
        WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@strFind+'%'
    END 

    --TO FIND STRING IN ALL FUNCTION        
    BEGIN
        SELECT ROUTINE_NAME           Function_Name
              ,ROUTINE_DEFINITION     Function_definition
        FROM   INFORMATION_SCHEMA.ROUTINES
        WHERE  ROUTINE_DEFINITION LIKE '%'+@strFind+'%'
               AND ROUTINE_TYPE = 'FUNCTION'
        ORDER BY
               ROUTINE_NAME
    END

    --TO FIND STRING IN ALL TABLES OF DATABASE.    
    BEGIN
        SELECT t.name      AS Table_Name
              ,c.name      AS COLUMN_NAME
        FROM   sys.tables  AS t
               INNER JOIN sys.columns c
                    ON  t.OBJECT_ID = c.OBJECT_ID
        WHERE  c.name LIKE '%'+@strFind+'%'
        ORDER BY
               Table_Name
    END
END

exec [util].[SearchAll]  'username'

Tuesday 20 April 2021

Find table and column name in MSSQL

It is time consuming job to browse each and every tables and columns to find required tables  and it's even difficult to search columns name in any database. There's a easy way to do the search.

For example, We want to search all the tables which contains user in the table names in sanjeebDB database:

select * from sanjeebDB.INFORMATION_SCHEMA.TABLES 

where TABLE_NAME like '%user%' 

     ORDER by TABLE_NAME  

Next example, we want to search all the tables which contains 'username' a column name:

select * from sanjeebDB.INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME  like '%username%'


If we need these functions repetitively then we can create a stored procedure and invoke the stored procedure passing keyword as a parameter:

 Create PROCEDURE [UTIL].[FIND_TABLE]
(
    @pTableName VARCHAR(max)
)
AS
BEGIN
     select * from sanjeebDB.INFORMATION_SCHEMA.TABLES 
     where TABLE_NAME like '%' + @pTableName + '%' 
     ORDER by TABLE_NAME  
END



create PROCEDURE [UTIL].[FIND_TABLE_BY_COLUMN]
(
    @pColumnName VARCHAR(max)
)
AS
BEGIN
     select * from sanjeebDB.INFORMATION_SCHEMA.COLUMNS where             COLUMN_NAME  like '%' + @pColumnName + '%'
END

To use them, run the below sql query:

exec util.FIND_TABLE 'user'

exec util.FIND_TABLE_BY_COLUMN 'username'


Good Luck!




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'


Thursday 11 February 2021

How to Enable Cors in .net standard API ( or .net core API) for PUT GET and DELETE Methods

 Error:

Access to fetch at 'https://localhost:44340/api/employee' from origin 'https://localhost:44339' has been blocked by CORS policy: Method PUT is not allowed by Access-Control-Allow-Methods in preflight response.


:44340/api/employee:1 Failed to load resource: net::ERR_FAILED


blazor.webassembly.js:1 crit: Microsoft.AspNetCore.Components.WebAssembly.Rendering.WebAssemblyRenderer[100]


      Unhandled exception rendering component: TypeError: Failed to fetch


WebAssembly.JSException: TypeError: Failed to fetch


  at System.Net.Http.WebAssemblyHttpHandler.doFetch (System.Threading.Tasks.TaskCompletionSource`1[TResult] tcs, System.Net.Http.HttpRequestMessage request, System.Threading.CancellationToken cancellationToken) <0x2fcb610 + 0x00a30> in <filename unknown>:0 


  at System.Net.Http.WebAssemblyHttpHandler.SendAsync (System.Net.Http.HttpRequestMessage request, System.Threading.CancellationToken cancellationToken) <0x2f9dd40 + 0x00174> in <filename unknown>:0 


  at Microsoft.Extensions.Http.Logging.LoggingHttpMessageHandler.SendAsync (System.Net.Http.HttpRequestMessage request, System.Threading.CancellationToken cancellationToken) <0x2f98030 + 0x00160> in <filename unknown>:0 


  at Microsoft.Extensions.Http.Logging.LoggingScopeHttpMessageHandler.SendAsync (System.Net.Http.HttpRequestMessage request, System.Threading.CancellationToken cancellationToken) <0x2f71090 + 0x00182> in <filename unknown>:0 


  at System.Net.Http.HttpClient.FinishSendAsyncBuffered (System.Threading.Tasks.Task`1[TResult] sendTask, System.Net.Http.HttpRequestMessage request, System.Threading.CancellationTokenSource cts, System.Boolean disposeCts) <0x3394518 + 0x00278> in <filename unknown>:0 


  at sanjeebblazorapp.App.Services.EmployeeDataService.UpdateEmployee (sanjeebblazorapp.Shared.Employee employee) [0x00051] in C:\Users\sanje\source\repos\sanjeebko\sanjeebtutorial-blazor\sanjeebblazorapp.App\sanjeebblazorapp.App\Services\EmployeeDataService.cs:42 


  at sanjeebblazorapp.App.Pages.EmployeeEdit.HandleValidSubmit () [0x001b0] in C:\Users\sanje\source\repos\sanjeebko\sanjeebtutorial-blazor\sanjeebblazorapp.App\sanjeebblazorapp.App\Pages\EmployeeEdit.cs:81 


  at Microsoft.AspNetCore.Components.ComponentBase.CallStateHasChangedOnAsyncCompletion (System.Threading.Tasks.Task task) <0x3395450 + 0x000da> in <filename unknown>:0 


  at Microsoft.AspNetCore.Components.Forms.EditForm.HandleSubmitAsync () <0x3307b00 + 0x00218> in <filename unknown>:0 


  at Microsoft.AspNetCore.Components.ComponentBase.CallStateHasChangedOnAsyncCompletion (System.Threading.Tasks.Task task) <0x3395450 + 0x000da> in <filename unknown>:0 


  at Microsoft.AspNetCore.Components.RenderTree.Renderer.GetErrorHandledTask (System.Threading.Tasks.Task taskToHandle) <0x2fb1590 + 0x000b6> in <filename unknown>:0 

Solution:

TO enable cors in .net core web API, cors policy need to be added in the services. Below code in blue color needs to be added in the Startup.cs file

public void ConfigureServices(IServiceCollection services)

        {

            

            services.AddDbContext<AppDbContext>(options =>

                options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

   

            services.AddCors(options =>

            {

                options.AddPolicy("Open", builder => builder.AllowAnyOrigin().AllowAnyHeader().WithMethods("PUT","DELETE","GET"));

            });


            services.AddControllers(); 

        }


 public void Configure(IApplicationBuilder app, IWebHostEnvironment env)

        {

            if (env.IsDevelopment())

            {

                app.UseDeveloperExceptionPage();

            }


            app.UseHttpsRedirection();


            app.UseRouting();


            app.UseAuthorization();


            app.UseCors("Open");


            app.UseEndpoints(endpoints =>

            {

                endpoints.MapControllers();

            });

        }

Tuesday 9 February 2021

Create User Group in Azure using Azure CLI (powershell)

Steps1 - Login to the azure account with  right account with azure AD access. 

Step 2 - Open CloudShell by clicking the icon on the top right corner.

Step 3 - You'll need to associate CLI session with a Storage account. So follow the instruction and connect with the storage account. 

Step 4 - Select the PowerShell in the dropdown window 


You'll see something like this screenshot:



Step 5 - type the below command and press enter:

 PS /home/sanjeeb> Connect-AzureAd

 Step 6 - type the below code (change the group name as your need):

PS /home/sanjeeb>New-AzureADGroup -Description "siteadmingroup" -DisplayName "siteadmingroup" -MailEnabled $false -SecurityEnabled $true -MailNickName "siteadmingroup"

On success, you'll see the object id like below: 

ObjectId                             DisplayName             Description

--------                             -----------             -----------

daf23147-8123-dc23-bsb8-d2d2a5224271 siteadmingroup siteadmingroup

Wednesday 27 January 2021

Generate Managed Identity Using Powershell for Azure DataFactory

Open the powershell in the azure. (Select the correct subscription and create storage location for powershell or select an existing storage account)  

Run the below query with correct value inside double quote: 

Set-AzDataFactoryV2 -ResourceGroupName "MYResourceGroup" -Name "mydatafactory" -Location "UK South"


Press enter, and you'll be prompted to confirm. Type Y and press Enter.

You should see output like below:

Confirm

A data factory with the name mydatafactory in the resource group MYResourceGroup exists.

Continuing execution may overwrite the existing one.

Are you sure you want to continue?

[Y] Yes  [N] No  [S] Suspend  [?] Help (default is "Y"): Y


DataFactoryName   : mydatafactory

DataFactoryId     : /subscriptions/e01105ad-822a-447a-2dd0-43124d16223l/resourceGroups/MYResourceGroup/providers/Microsoft.DataFactory/factories/mydatafactory

ResourceGroupName : MYResourceGroup

Location          : uksouth

Tags              : {}

Identity          : Microsoft.Azure.Management.DataFactory.Models.FactoryIdentity

ProvisioningState : Succeeded

RepoConfiguration :

GlobalParameters  :


For detail information read: Managed identity for Data Factory - Azure Data Factory | Microsoft Docs

Done!

Tuesday 26 January 2021

Create a batch file to copy/move files from network folder

Create a batch file (.bat) and paste the below code:

 @echo off

set DAYS=7

robocopy \\192.168.0.30\sanjeebapp\data\in E:\sanjeebapp\data\in\ /mov /maxage:%DAYS%

exit /b


Note: Change the source folder and destination folder as needed. /mov deletes the file from the source location . /maxage ignores the file which is older than DAYS. In this example, the files older than 7 days are not copied. 

Remove /mov from the script if you do not want to delete files in the source folder. 

 

Thursday 21 January 2021

Create Build/Release Pipeline for SSIS in Azure SQL server



Create a new pipeline in devops.
Select the source:
Select an empty template

Select Get Sources in the left panel:


Now add a new agent job to Build SSIS. Click the + icon.



Search for SSS job. (if it's not already installed, then first install it from the marketplace)
Add SSIS build and then add SSIS Deploy 
Once you add these two jobs, your screen should look like this:

Now Select Build SSIS:
in the Project Path field, select the directory where .sln file is located. 
Leave the rest of the fields as default.
Now Select Deploy SSIS job to configure deploying settings 

add source path = $(Build.ArtifactStagingDirectory) (i.e. output path value from Build SSIS job-Previous step).
select destination type = SSISDB.
Destination Server = realdatabaseserver01.database.windows.net
Destination Path = /SSISDB/masterdatahub
Authentication type = SQL Server Authentication (you can choose any other option as per your need)


Click on the Triggers tab and configure Continuous Integration. 


Now Save and Queue the Pipeline. 
Once released you will see the ssis package under masterdatahub folder



That's everything for Azure devops continuous integration/ continuous deployment process for releasing ssis project to azure sql server. Good luck!


Monday 11 January 2021

How to modify Login in Azure SQL server

 for example, the login name is 'mysqluserlogin'

1) Enable the disabled login:

ALTER LOGIN mysqluserlogin ENABLE;

2) Change the login password:

ALTER LOGIN mysqluserlogin WITH PASSWORD = 'thisismynewpassword*&£1';

3) Change the login name:

ALTER LOGIN mysqluserlogin WITH NAME = mysqlusernewlogin;

Generate SQL script from entity framework using powershell/visual studio

to run the below command , start PowerShell in visual studio. Select the database project. In PowerShell:   Script-Migration this command wi...