Microsoft Azure SQL Server Read Only User Creation Deletion

Hi folks,

Today I will explain, how you can easily manage the readonly (view) users in Azure Microsoft SQL Server

Procedure

In Azure SQL Server

CREATE LOGIN [testuser] WITH PASSWORD = 'random_p@$$w0rd';

For master and each DB

Note: You must create the user in master db before creating in other databases

CREATE USER [testuser] FOR LOGIN [testuser]   
    WITH DEFAULT_SCHEMA = [dbo];  
GO

Grant Connect permission

GRANT CONNECT TO [testuser]
GO

Give datareader role to read (view) only

ALTER ROLE db_datareader ADD MEMBER [testuser]
GO

Drop user in DB

DROP USER [testuser]

GO

Drop user in Azure SQL

DROP LOGIN [testuser];
GO

Please send your comments and feedback to psrdotcom@gmail.com

Free and discounted tools or software for Student Developers

Hi All,

Today I will share information of the tools which are free or less price for the student developers.

1. https://education.github.com/pack - Every student must subscribe to this
2. https://github.com/AchoArnold/discount-for-student-dev
3. https://github.com/ripienaar/free-for-dev
4. https://techcrunch.com/2019/08/20/github-adds-21-new-partners-to-its-free-student-developer-pack/
5. https://dzone.com/articles/20-pro-developer-tools-with-great-student-discount

Hope my links are useful for all the students.

And for the teachers also, the GitHub provides pack.

Please send your valuable feedback and comments to psrdotcom@gmail.com

Send email from Microsoft excel with customized sheet data

Hi folks,

Have you ever faced requirement where you have to send email from an excel with excel data. If you are in that situation, this blog makes your life easier.

Pre-requisites

Send customized excel data of each row as an email. You should have data in each row and you should know have recipient e-mail address

You should have configured e-mail client like outlook, mail of desktop

Procedure

1. Use Hyperlink function of excel
• Syntax
• =HYPERLINK("mailto:"&e-mailCellNo&"?subject="&subjectCellNo&"&body="&bodyCellNo&"%0A","Send email")
• Help
• Cellno in syntax should point to your data cell number
• %0A - New line
• mailto - The recipient mail address
• subject - the text
• body - the body text
• Note
• If you have any special characters, then use ENCODEURL function
• Don't put the ENCODEURL function in HYPERLINK function
• Make sure, ENCODEURL output points to a different cell and use that cell number in HYPERLINK
2. Use $ to use the same cell data repeatedly
3. After doing the hyperlink to a cell, you should be able to view "Send email"
4. Click on the send email
5. Opens the configured/default e-mail client by filling the recipient, subject and body content from excel sheet
6. Click on "send" or "Ctrl+Enter" keyboard shortcut to send email

Hope, this helps you to explore excel and send email with excel data to many recipients with single click

Please send your valuable feedback and comments to psrdotcom@gmail.com

Microsoft SQL Server 2019 Express Docker image Example

Hi Folks,

Today I am going to explain the procedure for connecting to a Microsoft SQL Server 2019 Express edition docker image

Pre-requisites

1. Docker Desktop
2. Windows OS
3. Powershell/command prompt

Procedure

Get and run docker image

> docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=<Your_Password>" -e "MSSQL_PID=Express" --name "<Your_SQL_Server_Name>" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest

Command information

Remove the "MSSQL_PID=Express" to run other version of SQL Server

Replace 2019 with required SQL Server version

Password should be atleast 8 characters with capital, small, numeric, special character combination

Use different port if you already have a local sql server

Name should not contain spaces

Check for docker container

> docker ps

You should able to see the container with your SQL Server name at the end in running status

Connect to SQL Server

docker exec -it "<Your_SQL_Server_Name>" /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "<Your_Password>"

You should be able to see "1>" prompt

Command information

Use the SQL Server name or user the container ID

Use database and play around with table(s)

Important

Multiple commands can be entered one after one, but to execute the set of command(s), you need give "GO" command.

Create Database

CREATE DATABASE SampleDB

GO

List all databases

SELECT Name from sys.Databases

GO

Start using the database

USE SampleDB

GO

Create table

CREATE TABLE UserInfo ( Id INT, Name VARCHAR(64))

GO

Insert values

INSERT INTO UserInfo (1, 'ABC')

GO

Retrieve table contents

SELECT * FROM UserInfo

GO

Exit from SQL Server

QUIT

Hope you are able to run the SQL Server Docker image.

Please send your feedback and comments to psrdotcom@gmail.com

Connect to public hosted Ubuntu server MySQL Docker Image from local MySQL Workbench

Hi folks,

Today I am going to explain the procedure to test the public cloud hosted MySQL docker in Ubuntu Server to your local MySQL WorkBench.

We configured a Ubuntu Server on public cloud and installed docker.
Ping the public IP address to make sure, it is reachable to your pc/laptop.

Configure System

Install MySQL Docker Image

# docker pull mysql/mysql-server:latest

Check the images

#docker images

You should be able to view the downloaded mysql server image

Start the MySQL Server

# docker run --name=mysql1 -p 33061:3306 -d mysql/mysql-server
-d : Run in daemon mode
-p host_port:container_port
--name: container name

The mapped public ubuntu server port is 33061, which is mapped to the mysql server port 3306.

Check the containers

# docker ps

You should be able to view your mysql docker containers with container id and ports information

Get the MySQL root user password

MySQL generates a one-time password for docker container and writes to the log.

• To view the log, execute the following command

# docker logs mysql1

• To get the generate one-time password, filter the log with keyworkd "GENERATED"

# docker logs mysql1 2>&1 | grep GENERATED

• You should be able to get the line like below

GENERATED ROOT PASSWORD: Axegh3kAJyDLaRuBemecis&EShOs

• Copy the one-time password. In the next step, we will use this to login.

Reset MySQL root user password

• Connect to MySQL server

# docker exec -it mysql1 -uroot -p

• Paste the generated root password
• Change the password

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'yourpwd';
mysql> FLUSH PRIVILEGES;

• Though you have the root user credentials, you won't be able to connect.

Root cause: root user belongs to localhost, not for the public host usage
Solution: create another user with all privileges

mysql> CREATE USER 'username'@'%' IDENTIFIED BY 'youpwd';
mysql> GRANT ALL PRIVILEGES on *.* TO USER 'username'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

• Exit the mysql

mysql> exit;

• Restart the docker

# docker restart mysql1

Connect from MySQL Workbench

1. Open MySQL Workbench
2. Click on add new connection icon
3. Enter the "Connection name"
4. Enter host name "public IP address" or "URL"
5. Enter username as "dev" (Above created user)
6. Click on Password "Store in vault" button and enter the "dev" user password
7. Click on "Test Connection" button
8. You should be able to view the "Successfully made the SQL Connection" pop-up

Hope this tutorial helps you to get connected with your public hosted mysql docker instance to your local mysql workbench.

Please send your feedback and comments to psrdotcom@gmail.com

View docker image file content with folder structure

Hi folks,

I have come across a scenario where I need to see the image contents.

Yes, I want to view the image content with hierarchy.

Then I found this dive, which has done it beautifully.

Install on Ubuntu PC

1. # wget https://github.com/wagoodman/dive/releases/download/v0.9.2/dive_0.9.2_linux_amd64.deb
2. # sudo apt install ./dive_0.9.2_linux_amd64.deb

Usage

• Get the docker images list
• # docker image list
• Choose the image which you want to explore by copying the "Repository" name
• Run the command
• # dive
• It takes some time to analyse and display the image contents in hierarchical manner.
• Of the left side, it will have the image layers
• Select the appropriate image layer with up/down arrow keys
• By pressing the "Tab" you will be able to switch to right side view where your files are available
• Use space to expand/collapse the folder
• Dive will display each folder size
• See the sample docker image contents with dive below

• To quit/exit from the dive, use Ctrl+C

Hope this is helpful

Please send your feedback and comments to psrdotcom@gmail.com

Run local file server with NPM http-server

Hi folks,

Today i'll explian the process of making your folder as a standalone file server.

Objective

Make my folder contents browse over http

Procedure

• Install Node
• Download from official website https://nodejs.org/en/download/
• Install http-server module in node globally
• npm install -g http-server
• Open terminal/command-prompt
• Navigate to your folder
• Run the following command to start the server
• http-server .
• or simply run
• hs .
• Run from anywhere with full path of the folder
• hs C:\FileFolder
• Starting up http-server, serving .
• Available on:
• http://10.10.6.2:8080
• http://127.0.0.1:8080
• http://youripaddress:8080
• Hit CTRL-C to stop the server
• You should be able to browse your file folder contents with any of the above URLs

Hope you are able to access your file contents over the browser

Tip: Using this approach, you can share your content over intranet/internet with full control over data as read only.

After stopped the server, access to the folder is stopped.

Please send your comments and feedback to psrdotcom@gmail.com