Log in failed, Error 18456 Severity 14 State 58 and more

 

 

So i had installed SQL Server 2012 on my machine and i had used it from Visual Studio all worked fine. Till i decided to give access of my database to one of my team mate. When he tried to login he got the following error.

I was confused , had the SQL login created and user with appropriate rights created on the database. But a quick look into server logs provided more details. The severity and state is not provided in the error message for security purposes. Just in case some one is trying to hack into your SQL Server.

 

As you can see from SQL Server Logs the severity is 14 and state is 58. Now the fix i am going to describe will work only for state 58. For other states you can refer to the following link.

The states have some form of description in codes. Although not elaborate you can make sense of the codes. Like in my case the state was 58 which WinAuthOnlyErr. This made it clear to me that when i had installed the SQL Server i did not set the authentication to mixed mode instead i kept it as windows only. Changing it back to windows was not that difficult at all.

All you need to do is follow few simple steps.

On the SQL Server do a right click and go to properties. This will take you to the following page

On the Server Properties go to security settings and change the Server authentication to SQL Server and Windows authentication mode.

After you have done this change restart the SQL Server Service. “ CAUTION: DO NOT Do this on live server without scheduling down time”

Also, the following page tells you how to trouble shoot the problem. Check the bottom of the page for detailed description of the states as well.

Finding UnUsed Tables in SQL Server

     

After being given a chance to have a look at the Database of a company for coming up with maintenance plan i had a peek into the DB. And to my astonishment i found over 60% of the table not being used. Regardless of the total number of tables existing in the DB the percentage of the unused table is too high to ignore. In this case total number of unused table was close to 1500. Now this is a classic example how badly people treat the SQL Server DB. You fatten it like a cow and then expect it to run like a horse?? Think again!!

Most of the tables were backup residuals. Clearly some one had backed up the data in table just in case if anything goes wrong and then forgot dropping the table. Or just created a table for one time reporting purposes and then never cleaned it up again. Further investigation also showed that there were few legacy tables with no rows. We will come back to this bit in a few.Lets get to the part where we find out how we get the unused tables.

Starting from SQL 2005 MS introduced something called as DMV or Dynamic Management Views. Every time we access the table either clustered or the Heaps it records the stats. The query is as below

Declare @DBName varchar(20)
set @DBName=DB_NAME()

Declare @SQL_Str nvarchar(1000)
set @SQL_Str='select o.name, i.type_desc FROM  ['+@DBName+'].sys.indexes i INNER JOIN ['+@DBName+'].sys.objects o ON i.object_id = o.object_id INNER JOIN ['+@DBName+'].sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN sys.databases sd ON sd.name ='''+  @DBName + ''''
set @SQL_Str =@SQL_Str +' LEFT OUTER JOIN '+@DBName+'.sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id AND ius.database_id = sd.database_id '
set @SQL_Str =@SQL_Str +' WHERE i.type_desc  in (''HEAP'',''CLUSTERED'') '
set @SQL_Str =@SQL_Str +' AND COALESCE(ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates) IS NULL AND sd.name <> ''tempdb'''
set @SQL_Str =@SQL_Str +' AND o.is_ms_shipped = 0 AND o.type <> ''S'''

EXECUTE sp_executesql @SQL_Str

I use the script above just set the variable @DBName to the set the current DB name.

Now, we have to be careful when we use this script. Although the SQL Server records the tables where we have accessed the data it wipes out the records every time the server has restarted. So be mindful about that . The rule of thumb which i have devised for myself is that allow the system to run for month and half which will mean that month end process if any have been run. Also, if there were people who were not in office have come back and used the system for few days. Again, going back to the point which i left above. Some dev’s have nasty habit of leaving the tables in join even without using the tables actively in query. What will happen is when you drop the table, the testers / users will complain of not being able to use the system.

Again, when it comes to dropping the tables we should just rename them to start their name with ‘z’. What this will do is push the table to the bottom and give you clear view of the tables to be dropped. Also what this does is if you happen to mark the tables which out of the blue is started to be used again you can simply rename the table and bring the system to working state. And once you have confirmed that the tables are no longer used you can make a note of the date when you had dropped the table so you can resurrect the tables from backup if required. This step should not be necessary but as you must have experienced ‘Never say Never’.  Another step you could take is to search your source code and stored procedures for the name of the tables. This is fairly cumbersome but sometimes necessary.

UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT

Today i tried to create a filtered index , i was able to create them alright. However, intrestingly when i tried to update record i got the following error
"UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations"

and when i tried to create new record the same

"INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations"



Why does this occur in first place. This is because by default ARITHABORT is set to OFF. So what does it mean when ARITHABORT is set to OFF. Quoting MSDN http://msdn.microsoft.com/en-us/library/ms190249(v=sql.105).aspx
"When ON is specified, a query is ended when an overflow or divide-by-zero error occurs during query execution.
When OFF is specified, a warning message is displayed when one of these errors occurs, but the query, batch, or transaction continues to process as if no error occurred." So basically we need o have the option on anyways. If we have it ON instead of the default OFF we will get the error instead of sweeping it off under the carpet. This may not be ideal for every one but it was for us in this case.

But what if you dont control the SQL Server settings and your DBA does not allow you to change the setting what then. Here is the link http://www.codeproject.com/Articles/55471/Working-Around-Issues-When-Using-SQL-Server-Indexe which could help to set the settings at the connection level.

Hope this helps any one who stumbles across this page.

SHOWPLAN permission denied in Database Engine Tuning Advisor

Last week one of my colleagues asked to me to provide ShowPlan rights. Now , all of the developers in our team are part of the group which is sysadmin on the test servers. So i was bit confused as to why the developer asked me to provide the ShowPlan rights.

On further investigation i found that she was trying to  run Database Engine Tuning Advisor. And she was getting the error [Microsoft][SQL Server Native Client 10.0][SQL Server]SHOWPLAN permission denied in database 'database'. When she was trying to execute the trace load.

Initially i thought that's easy will provide rights to the group using
grant showplan to [domain\user]  
and job done. But this was not the case.  To add to the confusion she was ( rightly ) tracing the statements using the application user. The application user again is not part of sysadmin.

There are things we need to take into consideration. When you catch the sql statements you want to tune using profiler you need to execute these statements using the sql / domain user who is a sysadmin user.
Also, on further reading through MSDN site i found that the very first time the Database Tuning Adviser needs to be run by user with Sysadmin permission or they should tune the statement first. After that all the other users who have SHOWPLAN permission will be able to use the DTA.

So the solution to my problem was now clear. I ran the DTA graphical interface with simple load of select * from ... and provided the showplan rights to the application user. Since this was test environment it was easy enough for me to provide the rights to the user.

Importing Contact Details from Excel Sheet to OutLook

This post is dedicated to Jenny & Sarah who posed this problem to me. This originally arose from another problem. We recently migrated to Office365 . There are limits on the number of emails you can send using Office365 services. We found out that sending emails to group is counted as sending email to single recipient. Now, our next problem was to create the email group from contacts in Excel sheet.
 The Steps to import Excel sheet is as follows
 1) The Excel sheet needs to be Excel 97-2003 workbook.
 2) Our Excel Sheet looks like this It’s always preferred that we don’t have the header. It will be easy for us in the next step. 

 3) We start with selecting all the cells which contain data. Now we have headers what I will be doing is removing it from the excel sheet. Go to first cell. Press Ctrl+ A this will automatically select all the cells which contain data. If we do not mark the cells we get error like The Microsoft Excel 97-2003 file "C:\folder1\folder2\abc.xls" has no named ranges. Use Microsoft Excel 97-2003 to name the range of data you want to import. 
 4) After selecting the data cells, we go to Formulas , then go to Define name as shown below 

 5) When you click Define it comes up with Dialog box; Enter name as Contacts

 



 6) Close the Excel file after saving it. Usually you if you don’t close it you will get error like Outlook was unable to retrieve the data from the file “C:\folder1\folder2\abc.xls". Verify that you have the correct file, that you have permission to open it, and that is not open in another program. 
 7) Open Out look, go to file then Import Select the Import from another program or file. Click Next Select Microsoft Excel 97-2003. Click Next 

8) Click Next Select Contacts , then click Next 

  9) Tick Import , when you tick import we get the mapping box.

 Now what we do is pick up the Elements and Drop them on to the elements on the Right hand side which results in the box below. We can populate up to 3 email addresses. The email addresses are at the bottom side of the box. Verify if the data is populated correctly by clicking Next button. 

After you are happy with the mapping, click on OK and wallah all the contacts from Excel Sheet will be in Contacts in OutLook

Funeral Blues

A lovely poem by W.H. Auden

Stop all the clocks, cut off the telephone,
Prevent the dog from barking with a juicy bone,
Silence the pianos and with muffled drum
Bring out the coffin, let the mourners come.


Let aeroplanes circle moaning overhead
Scribbling on the sky the message He is Dead.
Put crepe bows round the white necks of the public doves,
Let the traffic policemen wear black cotton gloves.

He was my North, my South, my East and West,
My working week and my Sunday rest,
My noon, my midnight, my talk, my song;
I thought that love would last forever: I was wrong.

The stars are not wanted now; put out every one,
Pack up the moon and dismantle the sun,
Pour away the ocean and sweep up the woods;
For nothing now can ever come to any good.

From the movie Four Weddings and a Funeral
on you tube

Cry Me A River lyrics

One of the songs i liked very much ...

Now you say you're lonely
You cry the whole night through
Well, you can cry me a river, cry me a river
I cried a river over you

And now you say you're sorry
For being so untrue
Well, you can cry me a river, cry me a river
'Cause I cried a river over you

You drove me nearly out of my head
While you never shed a tear, babe
Remember, I remember all that you said
You told me love was too plebeian
Told me you were through with me

And now you say you love me
Well, just to prove that you do
Why don't you cry me a river, cry me a river?
'Cause I cried a river over you, over you

You say you love me, but you lie

Now you say you love me
Well, just to prove that you do
Come on and cry me a river, oh, cry me a river
I cried a river over you, I cried a river over you

I cried a river, now you can too
Cry me a river, cry me a river