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

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

Query - to find objects containing specific word

Sometime back we did one exercise where our objective was to find out the objects which don't work any more .. 

for example suppose we had created a view v_tab with a simple select statement fetching abc column from XYZ table .. later on say after 3-4 months that abc column got renamed to lmn but the view v_tab referring to table column as abc never got updated so that view now became obsolete .. You see what happened there and mind you this happens quite often when attention is not paid while deploying new changes to the database objects.. 

we used following approach to address this

Query - to peek into Maintenance plan History

Today, it's about maintenance plan and it's history

Often we run into situations where jobs executing maintenance plan does not show actual error that caused the failure. And more often than not in such scenarios browsing through maintenance plans history works like a charm.. 

Recently a friend of mine returning from his long vacation and ran into similar situation where his sql agent job didn't provide me sufficient information about the root cause of failure. And to make matter worse they had limitation put on the agent history retention which did not help either. Thankfully he had error notification setup which helped him listing out the names of the job that had failed.

Given below is what I wrote to help him out .. there might be easier way than this so fill me in if you happen to know about it 

Interview Question - Write the SQL script to delete/Truncate data from all the tables of the specific database

Recently, I came across this question where candidate had to write a query to empty all the tables from the specific database. Prima facie it feels like a cakewalk for the candidate but believe me it's not. Why not ? Because one must consider the relationships between the tables while writing the query for this question. It could be multilevel hierarchy that one needs to identify before even thinking about deleting the data from the child tables.

So the question gets divided into 2 parts 

• Identify the relationship hierarchy
• Start deleting the data from the bottom i.e from child tables to the parent tables

Now the real question, is it really necessary to identify the hierarchy between tables ? Isn't there any other way to perform this ? 

Query - To list the jobs and their steps from where SSIS packages are getting executed- SQL 2008

Recently, I was asked to write a SQL query to fetch the information of the enabled jobs and their steps where SSIS package (stored in SQL server) is getting called directly.

As most of us know information related to jobs and other SQL agent related stuff is stored in the MSDB database in SQL server. There are quite a few tables which comes handy while trying to fetch the job details and honestly, I did not know where do I look for the required information. While browsing for the data I got to know about the following tables which provides all the necessary information 

• sysjobsteps - Stores the job step details 
• sysjobs - Stores the information related to jobs

Note - I noticed that when the package (stored in SQL server) is called from the job step, "command" column in the sysjobsteps starts with "/SQL". Following query is based on this observation only and I could be wrong in assuming that hence please feel to correct me in case I'm wrong. Also if there exists an easier way to find the job-package details please let me know..  

SQL Script

MS-CRM - Query to list the Activity record distribution across Business Units in the CRM database

The MS-CRM contains many entities of type Activity. Few of the most widely used activities are PhoneCall, Email, Appointment etc..

When the activity record is created in MS-CRM, it internally loads 3 tables with the provided information

1. Base table for the Activity. Ex. - AppointmentBase
2. ActivityPointerBase
3. ActivityPartyBase

ActivityPointerBase table contains one record for each activity record that is created in the MS-CRM. While ActivityPartyBase table hols the information of the parties (participants) for particular Activity record.

Following tables are used in the query to find the distribution of the activities across 

MS-CRM - Query to list the TimeZones for the Active Users

In MS-CRM database as we know end user can set the timezone of his choice for himself / herself. And after that all the data that end user sees on CRM UI would be displayed in the timezone that he/she has opted for. 

Now, the question is where does CRM stores the TIMEZONE related information? hence comes the TimeZoneDefinitionBase table to rescue. 

It holds the info such as 

• TimeZoneCode - Unique Integer value representing each timezone
• UserInterfaceName - Displays info in-terms of UTC +/- Hrs
• StatndardName - Gives away the global standard name for the TimeZone that everyone can understand

NULL with NOT IN () caluse

Most of you must be aware of the fact that NULL value causes more problem than anything else database world. Well here is an example which may or already have ruined few minutes @m some point in your career.

NULL means nothing then Why on the earth should we care about it ? well it is one of the most important thing that DB developer should care or you'd be doomed for unexpected results.

Let's see one example of NULL causing few of us headache.. 

MS-CRM - Query to list the Roles which are in use

I'm going to keep this blog post short and simple.. 

In case you are working on the MS-CRM and came across a situation wherein you want to list the roles that are currently in use in the particular organization then following query would help you out.

Objects used in the query are..

• SystemUserRoles - Holds the association between users of the CRM organization and the roles from it
• RoleBase - Lists the roles across CRM organization
• SystemUserbase - list of users present in the CRM system. Isdisabled column from the table tells us whether user is in enabled or disabled state

Please run this query against your CRM database. I am assuming here that you have READ privileges on the CRM database.

Query to find the object across databases

When a new member joins our team first thing is expected out of him or her is to understand the database structure in order to deal with the issues clearly. It takes time to understand the database structure completely until then they rely on us to answer their quest to locate the object. And most of the times they tend to ask you about the location of the object when you yourself are wandering in the complex code that someone else has written.

This is where a small script to list the objects from the database becomes handy.

Following script has helped me on numerous occasions to locate the objects see if it helps you as well...

MS-CRM - Query to list the Access Rights of the Role's on all the Entities

Recently, one of the Business Analyst associated with the CRM project wanted to know the access rights for all the roles across all the entities using T-SQL? The MS-CRM (4.0) database for which end user intended to fetch this information did not have any organization created on the top of it. Thus end users were not able to know what roles has what permission and on what entity...

I thought it would be good exercise to write a query to fetch this information. So I started writing one and at the end had following query ready with me.. It fetches required information for the roles which are associated with the Active users only. This means the roles which are not in use (or part of Inactive user) would not feature in the output of the query

Database Objects used in the query are listed below

• PrivilegeObjectTypeCodes - Contains the association between PrivilegeId and ObjectTypeCode of the entity
• PrivilegeBase - Contains the information about privileges such as Name, GUID etc..
• RolePrivileges -  Association between Roles and Privileges is defined here 
• RoleBase - Contains the information about Roles such as Name, GUID etc..
• EntityView - Has the information about Entities
• SystemUserRoles -  Association between Users and Roles
• SystemUser - Information about CRM users

MS-CRM - Query to List Roles assigned to Active users

This requirement came when I was working on the MS-CRM migration project and my client wanted to know ROLE's that ACTIVE users are part of....

In MS-CRM following objects hold this information

• SystemUserBase - Contains User related information. It also has a Isdisabled column which gives the information about which one's are active and which one's are not

• RoleBase - List of roles that are defined in the MS-CRM Organization

• SystemUserRole - defines the relationship between user and the role.

Following query will give fetch us the roles and the count of active users belonging to that role.

Interview Question - Write a Query to list the Prime numbers between 1 to 100

This one is something that was just tossed-up at me by my team lead. Honestly, it took me few seconds to recall what Prime Number is. And when I knew what I need to find, I started thinking about the logic to do it..

Prime Number - A prime number is a whole number greater than 1, whose only two whole-number factors are 1 and itself. In simple words, any Positive Number number which is divisible by Itself or 1. 

I could think of following ways to achieve it

Interview Question - How to combine result and display where the data is coming from

This one is the interview question faced by my friend during project allocation interview. 

We have two tables (tab1 and tab2) with following data in column ID1 and ID2 respectively.

 


The query needs to be written to produce the output given below

MS-CRM - Query to list the roles that user belongs to in a business unit

One day my client asked me to give him the list of CRM users and their roles as defined in the CRM organization. He also laid down couple of conditions 

• If a user belongs to more than one role then roles must be displayed as a comma separated string in a single row.
• We must know whether that user is in disabled state or not
• What is the business unit of the User

Luckily for me all this information was store in 3 objetcs (Table / view)

• SystemUser
• SystemUserRole
• Role

So given below is what I came up with

MS-CRM - Query to list the relationship between the Entities

In the recent past, I have been associated with multiple data migration projects. All of them had one thing common, the destination for all those projects was MS-CRM database. 

There were occasions when it was required to know the parent/child entity relationship before SSIS package for the migration could be designed. For this information, I used to bug CRM team of respective project every now and then, thanks to my exceptional remembering/recalling power and unfamiliarity with CRM Tool . :) 

CRM Team had enough of me one day and told me "So what you don't know CRM tool, entity relationship information can be extracted from CRM database as well." A
And it triggered me to write this script and believe me CRM Team was more happy than I was when I was done with it..

Way to replace individual characters from the string

In one of the project that I am associated with, we had a requirement to replace each occurrence of the characters from the input string by the characters of users choice.

For example -

Suppose we have a string - "Chetan Deshpande" 
and we have been asked to

• Replace D  by C
• Replace E  by O
• Replace S  by U
• Replace H  by N