How to connect SQL Server from Excel

How to connect SQL Server from Excel

How to connect SQL Server from Excel

This post can help you in understanding “How to connect SQL Server from Excel Workbook”. There are situations where we need to export data from SQL Server to Excel file. There are multiple ways to do that using BCP, Import-Export wizard, OPENROWSET, SSIS etc. But connecting SQL Server directly from excel might help in some situations:

  • Capturing a huge rows – Data Processing runs in background in excel
  • Connecting to a RDS / Azure instance
  • We can directly create a Pivot Table / Chart
  • No SQL Knowledge required

 

Steps: How to connect SQL Server from Excel Workbook

How to connect SQL Server from Excel

Continue reading

Posted in SQL Development | Tagged , , , , , , | 2 Comments

How to Script Login and User Permissions in SQL Server

How to Script Login and User Permissions in SQL Server

How to Script Login and User Permissions in SQL Server

Migrating login and user permissions to a new instance is one of the most common tasks for a SQL DBA. This post “How to Script Login and User Permissions in SQL Server“ can help you in scripting the login and the user permissions for a given database. Most of us knew that there is a script “sp_help_revlogin” available for migrating SQL Server logins which can also handle the passwords for SQL Server logins. Just to summarize the topic we are also giving the MS suggested script.

Server / Instance Level

  • Script Logins with Passwords
  • Script Login Server Roles
  • Script the Server Level Permissions

Database / Object Level

  • Script User Creation
  • Script User Database Roles
  • Script the Database Level Permissions
  • Script Object Level Permission

 

Here is the script for generating Login creation script for the given SQL Server instance. Here is the reference from the MS site. https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

There is another beautiful script provided by JP Chen and it also working for login migration.

Server / Instance Level:

 

Database / Object Level:

 

Summary:

The post “How to Script Login and User Permissions in SQL Server” can help you in transferring logins and users between SQL Server instances. Please do comment on the below comment box if you find any issues in code.

Download Scripts:

How to Script Login and User Permissions in SQL Server_Logins

How to Script Login and User Permissions in SQL Server_Users

Posted in SQL Scripts, SQL Server DBA | Tagged , , , , , , , , , , | 4 Comments

Get the Next and Previous Row Value in SQL Server

Get the Next and Previous Row Value in SQL Server

Get the Next and Previous Row Value in SQL Server

This post can help you to get the next and previous row Value in SQL Server using T-SQL. We had a requirement to get the previous row information and compare it with the current row value. Usually we do not suggest performing these operations at database side rather it can be done from application side as it easier the process in navigating row values. But in some cases we have to do it from back-end. Here is an example to get the next and previous row value in SQL Server using T-SQL script.

Download the script file: Get the Next and Previous Row Value in SQL Server

Posted in Interview Q&A, SQL Development, SQL Scripts | Tagged , , , , , , , , , , | 2 Comments