TIP - To fix orphan uers

Orphan Users

When a database is moved from one server to another "Orphan users" is one of those little things that tend to create problem in smooth transition. 

There could be 2 reasons because of which Orphan users might get created

• Associated Login for the user itself isn't present on the server where database has been moved 
• Mismatch between SIDs of USER of the database and the LOGIN of the server

When the database user looses it's association (SID mismatch) with Login it becomes orphan and this uncoupling leads to LOGIN loosing its privileges on that database.

In this tip we will try to build a script to address second reason mentioned above

sp_change_users_login 'report' - lists the orphan users from the databases

master.sys.server_principals / master.dbo.syslogins - lists the logins present on the server. 

Interview Question - To populate a table without specifying values in the INSERT statement

This one should be quick and most of must be aware of it. 

Following question was asked to one of my friend in an interview for the post of Sr. Software Engineer

How would you populate following table with 5 records without specifying values in INSERT statement ?

The table structure was as follows

IF OBJECT_ID('tempdb..#default') IS NOT NULL
DROP TABLE #default

CREATE TABLE #default
(
id INT IDENTITY(1,1),
software VARCHAR(20) DEFAULT('microsoft'),
osType VARCHAR(10)
)

GO

Interview Question - What will be the output of .... ? LEN() function

This blog is about what happens when we do not pay attention to Microsoft documentation ... 

Recently, I had a simple question fielded to me which made me look STUPID.. Well being stupid is my right as a human being :) but making me look one is a different thing altogether ... I hope you guys are with me on this :) .... Here comes the bazoooooooooka 

What will be the output of following SQL statements ?

• SELECT LEN(' ') as 'single Tab' 
• SELECT LEN('    ') as 'multiple spaces' 
• SELECT LEN('    ') as 'tab followed by 3 spaces' 
• SELECT LEN(' ') as '3 spaces followed by tab' 
• SELECT LEN('   a') as '3 spaces followed by char' 
• SELECT LEN(' ') as 'space tab space' 
• SELECT LEN(' a ') as 'space char space' 

Simple isn't it ....

Well if you guess the output of each of these correct then BRAVO, if not then you'll know how does it feel when simple thing such as this stumps you..

Query - to script the steps to move user database files around

Many times we don't pay attention while creating a user database and end up creating database files where they are not supposed to be.. in such scenarios often we find ourselves moving file across drives so as to arrange them to match our settings (or liking's ?) 

To move files of the online user database, you have to perform certain steps in the specific order and it is documented quite nicely in MSDN

This is what we do

• Take DB offline
• Move files
• Modify old file location to the new one
• bring database online

The following script generates the statements for the  steps mentioned above.. Please note that on my server I wanted to move files for the databases with a name starting from test.. you will have to change the WHERE clause so that the scripts are generated for the databases of your choice

Query - to find specific column across tables and across database

Here is the situation my friend came up with -

list all the databases and tables with containing one specific column

he required this info because his project was under migration and they were looking for this kind of information .. The query shared here can be modified to find Most commonly used column name across databases

Query does 2 things

• Loops over databases having test at the start of their name
• Finds out the column across tables using Information_schema.Columns