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 , , , , , , , , , , | Leave a comment

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

SQL Server Date and Time related Interview Questions

SQL Server Date and Time related Interview Questions

SQL Server Date and Time related Interview Questions

This post can help you in answering SQL Server Date and Time related Interview Questions.  Below is the list of queries which are recently asked in SQL Server Developer interviews. These are not just for interview preparation also helps you as a quick reference while working on date & time related queries.

Along with these it would be good if you can refer the most commonly used DATETIME data types (DATE, TIME, SMALLDATETIME, DATETIME, DATETIME2, and DATETIMEOFFSET) and functions (GETDATE, SYSDATETIME, CONVERT, DATENAME, DATEPART, DATEDIFF, DATEADD, EOMONTH etc.)

 

Logic Explained:

When you are asked to write a query in an interview, you need not give the exact output rather you are expected to give the logic. The person who understands the base logic can easily implement a solution for any given requirement. Now let’s try to understand the logic.

Here ‘0’ indicates the starting date in the data time range. i.e : 1900-01-01 00:00:00.000

SELECT DATEADD (DD, 0, 0);

Now we’ll see how to get the first day of current week:

SELECT DATEADD (wk, DATEDIFF (wk, 0, GETDATE ()),0) AS ‘Current_Week_First_Day’

DATEDIFF (wk, 0, GETDATE()): First we are capturing the total number of weeks between the start date in date range (1900-01-01) and the current date (Based on the GTEDATE). For example today’s date is 24-Jan-17 then the difference will be (SELECT DATEDIFF (wk, 0, ‘2017-01-24’) ; 6108.

DATEADD (wk, DATEDIFF (wk, 0, GETDATE ()), 0): We have calculated the inner value as 6108. Now we simply add the number of weeks to the start date then it fetch the 6108 week starting date. i.e.: SELECT DATEADD (wk, 6108, 0); returns the week start date as “2017-01-23 00:00:00.000”

SQL Server Date and Time related Interview Questions  Download the T-SQL script file.

Posted in Interview Q&A, SQL Development, SQL Scripts | Tagged , , , , | 1 Comment