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

Tip - Query to list the accounts running the SQL services

It will be quick this time around ..

Have you guys ever encountered a situation when you badly had to know the account name under which some remote SQL server and SQL server Agent services are running. 

Ahh you did, great.. So what did you do ? went to SQL box and found out it using services.msc or SQL config manager ?  alright Good. 

Let me make situation a bit more challenging ..

What if you can not remote into SQL BOX because of limited access? Now what will you do ?Pretty frustrating isn't it. Just to know the account name for the SQL service you have to raise a ticket with IT and wait for them to respond. 

But what if I tell you, when you can query that server fine why bother jumping onto the server itself? Yes,it can be achieved using simple SQL statement. Isn't that awesome? if you ask me.. YES, it is.

Starting SQL 2008 R2  MICROSOFT has solved this problem for us and presented 

• dm_server_services - The dynamic management view to report status information about the services

Well how about that.. let us see it in action