Archive
drop failed for login test or not able to drop a login in SQL server
ERROR:
i wanted to drop a TEST login that i have created some days back ..
i was getting below error
i checked and closed all query window connections and also all SSMS object explorer connection from this particular login
but still i got the same error again ….
CAUSE ::
i looked into the results of sysprocesses to see if anything is still connected VIA TEST login ..
i Figured out a program named Microsoft SQL Server Management Studio – Transact-SQL IntelliSense
was connected to Server VIA .. login named TEST .. that is to say intelliSense remains connected to SQL server long after the the query analyzer and SSMS connections are explicitly closed ..
i used below query to kill .. these IntelliSense connections and i was able to drop the TEST login
select ‘KILL’ + ‘ ‘ + cast (spid as char(5)),*
from sysprocesses where spid > 50 and program_name like ‘%IntelliSense%’ and loginame like ‘name of the login you want to drop’
KILL 88
KILL 104
enjoy 🙂
bangalore-user-group-meeting
Hey guys check out cool coverage by Balmukund on bangalore-user-group-meeting
Enjoy 🙂
alter index failed for SCOM OperationsManagerDW
Error :: something similar to below
Executing the query “ALTER INDEX [PK__Performa__AFAD0EB40688E6B3] ON [P…” failed with the following error: “Cannot find index ‘PK__Performa__AFAD0EB40688E6B3’.”.
Executing the query “ALTER INDEX [PK__EventSta__95632343599644AB] ON [E…” failed with the following error: “Cannot find index ‘PK__EventSta__95632343599644AB’.”
Cause of error is an in build SCOM functionality that drops and recreates tables named Event Stage (also the associated primary keys) every 60 secs (refer below )
Solution ::
1> you may excluded the database named OperationsManagerDW from re-indexing jobs as this particular DB has its own building maintenance feature that runsEvery 60 secs
enjoy 🙂
User account restriction. Possible reasons are blank passwords not allowed, logon hour restrictions, or policy restriction has been enforced
Error :
User account restriction. Possible reasons are blank passwords not allowed, logon hour restrictions, or policy restriction has been enforced
First and foremost ::
please check whether PASSWORD for the ACCOUNT is expired ? if so please reset it and try again (before going ahead with below steps)
Solution::
1. Click the Start button and type gpedit.msc in the Search programs and files bar and hit enter.
2. At the left pane, go to Local Computer Policy > Computer Configuration > Windows Settings > Security Settings > Local Policies > Security Options
3. Look for “Accounts: Limit local account use of blank passwords to console logon only” and double click on it.
4. By default the Enable option is selected and all you need to do is select “Disable” and click OK.
Now you can connect to the computer using Remote Desktop and login to the user account that don’t have a password. Some Windows such as the XP home doesn’t have gpedit.msc and you will have to make the changes from registry.
1. Type regedit at the Search programs and files bar and hit enter.
2. Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
3. Look for LimitBlankPasswordUse name on the right pane, double click on it and set the value data to 0.REF :: http://www.top-password.com/blog/how-to-fix-logon-failure-user-account-restriction/
OR
go to run type regedit.exe
then go here HKLM\System\CurrentControlSet\Control\Lsa\limitblankpassworduse
change from 1 to 0ENjoy:)
An invalid schema or catalog was specified for the provider “SQLNCLI10” for linked server
ERROR: while retrieving data over linked server … you receive below error
Msg 7313, Level 16, State 1, Line 2
An invalid schema or catalog was specified for the provider “SQLNCLI10” for linked server “LinkedServername.DBName .dbo.tablename”
Cause ::
the database named DBName on target server .. that is the server to which your linked server is pointing contains a SPACE in its name
so in reality the database name is ” DBNAME ” that is it has leading or trailing spaces in its name …
steps to resolve the same
1>USE DBNAME;
GO
2> ALTER database DBNAME set single_user with rollback immediate
go
3> EXEC sp_renamedb ‘DBNAME ‘, ‘DBNAME123‘
go
4> EXEC sp_renamedb ‘DBNAME123’, ‘DBNAME‘
above script will resolve the linked server error :
if you try to run below script and by pass step no. 3 .. you will receive an error saying database named DBNAME already exist
EXEC sp_renamedb ‘DBNAME ‘, ‘DBNAME‘ (SQL need the new name to be different from the old name )
so follow step 1 to 4 .. to resolve error similar to below :: enjoy 🙂
An invalid schema or catalog was specified for the provider “SQLNCLI10” for linked server
SQL DBA learning curve 