Unable to login into SQL Server 2000/2005/2008 ecause forgot sa password and dont have Windows/Integrated security account access

Problem:
=======
You are unable to login into SQL Server 2000/2005 or 2008 because you do not have access through Windows account and your SQL account is locked or you have forgotten the password

Errors:
=======
Different errors you may get: as you attempt through various ways:

Error 1:

TITLE: Connect to Server
------------------------------
Cannot connect to abcsql\sql.
------------------------------
ADDITIONAL INFORMATION:
Login failed for user 'test'. Reason: The account is disabled. (Microsoft SQL Server, Error: 18470)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18470&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Error 2

TITLE: Connect to Server
------------------------------
Cannot connect to abcsql\sql.
------------------------------
ADDITIONAL INFORMATION:
Login failed for user 'test' because the account is currently locked out. The system administrator can unlock it.  (Microsoft SQL Server, Error: 18486)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18486&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------

Solution:
=======
Solution 1: Simple and easy
=====================

If you have not deleted the builtin\adminsitraors account from SQL login explicitly, logon to the Server/Machine with one of the builtin\administrators of the machine and you should be able to connect with SQL Server

Solution 2
=========

1. Stop SQL Server and SQL Server Agent

2. Start SQL Server from cmd with -c -m

3. Connect to SQL instance using sqlcmd or SSMS (timing here is important, you need to make sure that no one else is able to login before you connect, even default trace) and as you connect run a command similar to below:

sqlcmd -S abcsql\sql -i"D:\abc.sql"

where abc.sql should look like this:

exec sp_password @new='p##JPMch@se123', @loginame='sa'

go

alter login sa enable

go

Make sure that the password meets Windows password requirement, else the change will fail in case "Enforce password policy" is turned on

Ideally, the above should fix the issue for you.

However, in this case since "Enforce password policy" was turned on, the sa account was locked, so back to square one, and I had to struggle again with timing to unlock the account using query similar to below:

sqlcmd -S admin:abcsql\sql -i"D:\xyz.sql"

where xyz will look something like below:

alter login sa WITH CHECK_POLICY = OFF

alter login sa WITH CHECK_POLICY = ON

go

You should be able to get in now :)

Don’t forget to add the required accounts and start SQL as a service

During the above you may need to restart sql several times in case someone else gains login before you.

The server principal "abc" is not able to access the database "userdatabase" under the current security context. (Microsoft SQL Server, Error: 916)

TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The server principal "abc" is not able to access the database "userdatabase" under the current security context. (Microsoft SQL Server, Error: 916)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1600&EvtSrc=MSSQLServer&EvtID=916&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
 

Problem
=======
I have a login which has access to one of the databases on the SQL Server instance but not all, when the user connects to SQL Server remotely and tries to expand databases it gets the message that it does not has access to one of the user databases (on which it actually should not have access) and in the process user is also unable to access his/her own database as they can't get past the error

Error:
=======

TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The server principal "abc" is not able to access the database "userdatabase" under the current security context. (Microsoft SQL Server, Error: 916)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1600&EvtSrc=MSSQLServer&EvtID=916&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------

Reason
========One of the databases to which the user did not had access to had autoclose on, when we try and expand databases in SQL Server Management Studio, it tries to at least start all the databases and since the user abc in my case did not had access to the the database "userdatabase" he was getting an error. A quick analysis of trace file collected by Profiler will show you something like below:

 

2011-03-08 11:55:32.20 spid67      Starting up database 'Userdatabase'.

Followed by the error

 

Solution:

===========

Turn off Autoclose on the database userdatabase and you should be able to connect now

 

More Information

===============

 

If Autoclose is set to true, the every time you would try and connect to the SQL Server it would attempt to start the database which may cause issues if you don't have access to the database. SQL Server need to start the database because it needs to collect basic information about the database before it can show it under the databases TAB in SSMS.

 

  --Abhishek Soni

Trigger to audit permission changes in SQL Server 2005 on any object in any database

Create a table to maintain the records in master database because its a server level trigger:

===========================================================

create table dbo.PermissionAudit (eventtype nvarchar (100), serverLogin nvarchar(100) not null,DBUser nvarchar (100) not null,TSQLText varchar (max), eventdata xml not null)

Trigger Definition

===================== 

Create TRIGGER PermissionAudit

ON All Server

FOR

CREATE_LOGIN,ALTER_LOGIN, DROP_LOGIN, ALTER_AUTHORIZATION_SERVER,GRANT_SERVER,DENY_SERVER, REVOKE_SERVER,

AS

      DECLARE @Eventdata XML

      SET @Eventdata = EVENTDATA()

      INSERT into

            dbo.PermissionAudit

(EventType,EventData, ServerLogin,DBUser,TSQLText)

            VALUES

            (     @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),

                  @Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),

                  @Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')

            )

Enabling the Trigger

======================

enable trigger PermissionAudit on all server

--Abhishek Soni

Script to find out size of all the databases on a particular SQL Server Instance

select @@SERVERNAME servername, DB_NAME(db.dbid) DatabaseName, Sum(af.size)*8/1024 DBSizefrom sysdatabases db inner join sysaltfiles af on db.dbid = af.dbid where DB_NAME(db.dbid) not in ('master', 'tempdb','model', 'msdb', 'distribution') Group By DB_NAME(db.dbid)

Comments:
==========
The output would have the following columns:

ServerName
DatabaseName
Size (in MB)


--Abhishek Soni

Script to find out free space for each file in every database on a particular SQL Server Instance

SET NOCOUNT ON IF OBJECT_ID('tempdb..#DBINFO') IS NOT NULLBEGINDROP TABLE #DBINFOEND CREATE TABLE #DBINFO (dbname sysname,lname sysname,TYPE varchar(20),[size] decimal(9, 2) NULL ,[used]
)decimal(9, 2) NULL IF OBJECT_ID('tempdb..#LOGINFO') IS NOT NULLBEGINDROP TABLE #LOGINFOEND CREATE TABLE #LOGINFO(DBName sysname,LogSize real,LogSpaceUsed real,Status int) IF OBJECT_ID('tempdb..#TEMPSTATS') IS NOT NULLBEGINDROP TABLE #TEMPSTATSEND CREATE TABLE #TEMPSTATS(fileid int,filegroup int,totalextents int,usedextents int,name varchar(1024),
)filename varchar(1024) DECLARE @dbname sysname,@sql varchar(8000) IF OBJECT_ID('tempdb..#temp_db') IS NOT NULLBEGINDROP TABLE #temp_dbEND SELECT
name INTO #temp_dbFROM master.dbo.sysdatabases

WHERE DATABASEPROPERTY(name,'IsOffline') = 0AND has_dbaccess(name) = 1ORDER BY name WHILE (1 = 1)BEGIN



SET @dbname = NULL SELECT TOP 1 @dbname = nameFROM #temp_dbORDER BY name IF @dbname IS NULL

TRUNCATE TABLE #TEMPSTATS
INSERT INTO #TEMPSTATS
EXECUTE(''DBCC SHOWFILESTATS'')
INSERT INTO #DBINFO (DBNAME, LNAME, TYPE, [SIZE], [USED])
SELECT db_name(), NAME, ''Data''
, totalextents * 64.0 / 1024.0
, usedextents * 64.0 / 1024.0
FROM #TEMPSTATS
INSERT INTO #DBINFO (DBNAME, LNAME, TYPE, [SIZE], [USED])
SELECT db_name(), name, ''Log'', null, null
FROM sysfiles
WHERE status & 0x40 = 0x40'

GOTO _NEXT SET @sql = ' USE ' + @dbname + ' EXEC(@sql) DELETE FROM #temp_db WHERE name = @dbnameEND _NEXT: INSERT
INTO #LOGINFOEXECUTE ('DBCC SQLPERF(LOGSPACE)') UPDATE

#DBINFOSET SIZE = B.LogSize, USED = LogSize * LogSpaceUsed / 100FROM #DBINFO AINNER
JOIN #LOGINFO BON (A.DBNAME = B.DBNAME)AND(A.TYPE = 'LOG') SELECT dbname AS [database name],lname AS [LOGICAL FILE NAME],TYPE,[size] AS [space allocated (MB)],used AS[space used (MB)],[size] - used AS [free space (MB)],cast(used/[size]*100 AS numeric(9,2)) AS [space used %],cast(100-(used/[size]*100) AS numeric(9,2)) AS [free space %]FROM #DBINFOORDER BY dbname, TYPE DROP TABLE #DBINFODROP TABLE #temp_dbDROP TABLE #TEMPSTATSDROP TABLE #LOGINFO SET




Comment
===========
The script would return the following columns:

database name
logical file name
TYPE (data or log)
space allocated (MB)
space used (MB)
free space (MB)
space used %
free space %


--Abhishek Soni NOCOUNT OFF

Job running SSIS or DTS package in SQL Server hangs when using compression in one of the steps (WINZIP, WINRAR etc)

Problem
=======
SQL Server Job which calls a SSIS or DTS package may be hung if you are using any compression software in one or some of the steps

Cause:
========
There may be a pop up from the compression software being opened in the background which waits for response, since you would not notice this when the job is running the job would end up waiting indefinitely on it and you would not get any error

You may run into same issue for any package which might be using a logic/software which gives pop up. In my case the job had been running fine for a while before it started to hang, finally I found out that Winzip had been uninstalled from the server and when it was being called in the package a pop up would appear with Winzip installation wizard. Once I installed Winzip manually the job reverted to normal behavior.

Solution
=======
To find out if the job is actually victim of a pop up dialog box in the background, run the package manually using BIDS (Business Intelligence Development Studio) or DTS Designer and you should get the pop up.

You could also run the package from the command prompt using a command similar as below and you should see the pop up:

1. Go to the path where dtexec.exe is present and run the following:

Syntax
------------

dtexec.exe /f "<complete location of the package with name>" >d:\packagelog.txt

Example
------------
dtexec.exe /f "D:\SSISpackages\abc.dtsx" >d:\packagelog.txt


As per the above command the log for the package would be saved to D drive as packagelog.txt


--Abhishek Soni

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created

Problem
=======


Message:
=======
Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created
The above dialog box appears in SQL Server 2008 Managemnet Studio (SSMS 2008) when you try and make changes to a table which reuire the table to be dropped and recreated internally.

Solution
========
To get rid of the dialog box follow the below steps to change the SSMS 2008 setting:

On  the Tools menu, click Options, expand Designers, and then click Table and Database Designers. Select or clear the Prevent saving changes that require the table to be re-created check box.

The above is shown the image below:

More Information
==============

The following actions might require a table to be re-created:

• Adding a new column to the middle of the table
• Dropping a column
• Changing column nullability
• Changing the order of the columns
• Changing the data type of a column

--Abhishek Soni