Archive for January, 2014

How to resolve the Windows 7 Error 0×80070522 “A required privilege is not held by the client.”?

Speaking of frustration!: You are the owner of your laptop as you have spent more than a thousand dollars on it and you have made sure you are the administrator or your user account is belonging to the administrator group. When you try to create or paste a text file into your C:\ drive, Windows throws you the error

Error 0×80070522 “A required privilege is not held by the client.”

It is similar to being locked out of your own car or having so much spams on your WordPress blogs that you are disabling the comments.

This reminds me of the funny DIRECTV commercials.

You are upset! When you are upset you start Googling how to resolve the problem. When you start Googling, you get misleading answers by the experts who are trying to increase their points in the various websites that give credits for the first responders or voted by some people as the correct answers when they have not even tried the solutions proposed. When you follow those advice to change the security settings or change ownership of the C:\ drive, you cause your computer to lock you out of the C:\ drive -> deny you access to your own C:\ drive. Think twice before follow those advice. Try the following instead:
1. Click Start > Run > SECPOL.MSC.
2. Once the “Local Security Policy” window opens, under “Security Settings” > Local Policies > Security Options.
3. Scroll down to find “User Account Control: Run all administrators in Admin Approval Mode” and then disable it. Yes -> disable it. Then, click the OK button.
4. Restart your computer.

How to query the replication information in a minute or less?

What if you have just been assigned as the new DBA in company XYZ and you need to find out the transactional replication setup in a New York minute. What system procedures and query can you utilize to help you?
In the past, I have used the following two system procedures and query to help me find the replication setup information under a minute. I hope they can help you do the same:
1. SP_HELPDISTRIBUTOR
This system store procedure can be executed on the publication database or any other databases in the publisher and it will return the information about the distributor, the distribution database, working directory, and the SQL Server Agent user account in addition to other useful distribution related information.

-- run it on the publisher
sp_helpdistributor

2. SP_HELPSUBSCRIPTION
This system store procedure has to be executed on the publication database in the publisher and it will list the subscription information associated with the publications, articles, subscribers, and subscriptions.

-- run it against the publisher database
sp_helpsubscription

3. Querying the distribution database

USE Distribution -- replace this with your distribution database
GO 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

SELECT DISTINCT  
SRV.srvname publication_Server  
, Articles.publisher_db 
, Publications.publication publication_name 
, SS.srvname subscription_server 
, Subscriptions.subscriber_db 
FROM MSArticles Articles  
JOIN MSpublications Publications ON Articles.publication_id = Publications.publication_id 
JOIN MSsubscriptions Subscriptions ON Publications.publication_id = Subscriptions.publication_id 
JOIN master..sysservers SS ON Subscriptions.subscriber_id = SS.srvid 
JOIN master..sysservers SRV ON SRV.srvid = Publications.publisher_id 
JOIN MSdistribution_agents DistricutionAgent ON DistricutionAgent.publisher_id = Publications.publisher_id  
     AND DistricutionAgent.subscriber_id = Subscriptions.subscriber_id 
ORDER BY publication_Server, Articles.publisher_db, publication_name 

You can then save these information in an Excel file for further analysis or recording keeping purposes.

REFERENCES:
SP_HELPDISTRIBUTOR
http://technet.microsoft.com/en-us/library/ms177504(v=sql.105).aspx

SP_HELPSUBSCRIPTION
http://technet.microsoft.com/en-us/library/ms190493(v=sql.105).aspx

How to look up the hostname based on the IP address?

During troubleshooting, you have reviewed the SQL Server Error log and found the error messages have the ip address appended to them like the following:

Login failed for user ‘DOMAIN\user’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 123.45.6.789]”

You may have asked yourself: Wouldn’t it be nice if Microsoft had helped us all out by including both the ip address and the hostname in the error message? Yes it would ideal. But, as with everything else in life – it’s not supposed to be perfect. You have to work your way to get what you need.
To look up the host named based on the ip address you will need to use the NBTSTAT command.
NBTSTAT

Implementation Steps:
1. Find the ip address in the error message or just simple get an ip address you wish to look up its host name.
2. On the command prompt, key in NBTSTAT –A ###.###.###.###. Then, press enter. Where ###.###.###.### is the ip address you wish to resolve to a hostname.

REFERENCE:
http://technet.microsoft.com/en-us/library/cc940106.aspx

How to resolve the “The server principal “Domain\LoginName” already exists” Error Msg 15025, Level 16, State 2, Line 1

Today, I have restored a database on a new SQL Server instance from a backup that was extracted by the Windows Admin team. After verifying that all the post restore steps are completed correctly, I have notified the QA team to start their testing. The QA team has called and notified me that the login failed during testing. The QA engineer has shared with me the following:
1. She’s attempting to logon using the account DOMAIN\Liz.
2. She has received the error:

“Cannot open database “DatabaseName” requested by the login. The login failed.
Login failed for user “DOMAIN\Liz”.

After verifying that the login does not exist in the instance and user does not exist in the database, I have attempted to create the login. But, it has failed and reported:

Msg 15025, Level 16, State 2, Line 1
The server principal “DOMAIN\Liz” already exists.

Just to make sure I have not misread the error, I have queried sys.server_principals system table. But, unable to find the login “DOMAIN\Liz”
To resolve this issue, I have:
1. Find the security identification number (SID) using the SUSER_SID() function.
2. Query the sys.server_principals by specifying the SID found in step 1. It has returned “DOMAIN\Elizabeth”. The user has confirmed that her old login name.
3. Drop the login identified.
4. Create the login again and this time it is successful.

 

Implementation steps:

1. Find the security identification number (SID) using the SUSER_SID() function.

SELECT SUSER_SID('DOMAIN\Liz')
-- It has returned 0x010500000000000515000000D114792BA53AB44C8C29BC2F1F505700

2. Query the sys.server_principals by specifying the SID found in step 1. It has returned “DOMAIN\Elizabeth”. The user has confirmed that her old login name.

SELECT *
FROM sys.server_principals
WHERE sid = 0x010500000000000515000000D114792BA53AB44C8C29BC2F1F505700
-- It has returned DOMAIN\Elizabeth

3. Drop the login identified and create a new login.

DROP LOGIN [DOMAIN\Elizabeth]
GO
CREATE LOGIN [DOMAIN\Liz]  FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

 

REFERENCES:
SUSER_SID ()
http://technet.microsoft.com/en-us/library/ms179889(v=sql.105).aspx
sys.server_principals
http://msdn.microsoft.com/en-us/library/ms188786(v=sql.105).aspx