March 2023
M	T	W	T	F	S	S
	1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31

Archive for the ‘Security’ Category

Help : Who dropped login from server?

Posted by blakhani on September 23, 2014

Long time ago I blogged about Who dropped objects from database and it was very useful for many readers. Few days back someone asked me via Facebook – “Is it possible to find who dropped login from server?” Initially I check the “Schema Change History” report but it was not showing expected output. I went ahead and looked into default trace and found that there are some entries when I delete a login. Since the data was available in default trace, I have written below script to read those files and show them in readable format.

DECLARE @enable INT

SELECT TOP 1 @enable = convert(INT, value_in_use)
FROM sys.configurations
WHERE NAME = 'default trace enabled'

IF @enable = 1 --default trace is enabled
BEGIN
    DECLARE @d1 DATETIME;
    DECLARE @diff INT;
    DECLARE @curr_tracefilename VARCHAR(500);
    DECLARE @base_tracefilename VARCHAR(500);
    DECLARE @indx INT;

    SELECT @curr_tracefilename = path
    FROM sys.traces
    WHERE is_default = 1;

    SET @curr_tracefilename = reverse(@curr_tracefilename)

    SELECT @indx = PATINDEX('%\%', @curr_tracefilename)

    SET @curr_tracefilename = reverse(@curr_tracefilename)
    SET @base_tracefilename = LEFT(@curr_tracefilename, len(@curr_tracefilename) - @indx) + '\log.trc';

    SELECT TargetLoginName 'Login which was dropped'
        ,StartTime 'When'
        ,LoginName 'who dropped'
        ,ApplicationName 'from where'
        ,HostName 'machine name'
    FROM::fn_trace_gettable(@base_tracefilename, DEFAULT)
    WHERE EventClass IN (
            104                    -- SQL Login would have "Audit Addlogin Event"
            ,105                -- Windows Login would have "Audit Login GDR Event"
            )
        AND EventSubclass = 2 --drop
END
ELSE
Select 'Default trace is not enabled'

Hope this would help some DBA to find culprits and punish them.

Cheers,
Balmukund Lakhani
Twitter @blakhani
Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

Posted in Script, Security | Tagged: audit addlogin, audit login GDR, default trace, who dropped login | 7 Comments »

Did you know? How non-sysadmin can change their own password in SQL Server?

Posted by blakhani on February 25, 2014

How would you feel if you are not able to change password of your own account? In almost all website, every login/user can change his/her own password. An Administrator need not change password for everyone and keep whispering via email. Login in the SQL Server should also be able to do so. Correct? Let’s try.

I have created a SQL login in SQL Server using below T-SQL.

USE [master]
GO
CREATE LOGIN [SQLServer-Help]
    WITH password = N'Initial@Password'
GO

There is no super power given to the account SQLServer-Help. He is part of pubic role. I have logged into SQL Server using this account and the password. I was surprised to see that my attempt to change my own password failed.

USE [master]
GO
ALTER LOGIN [SQLServer-Help] WITH PASSWORD=N'pass@word1'
GO

Here is the error which I received

Msg 15151, Level 16, State 1, Line 1

Cannot alter the login ‘SQLServer-Help’, because it does not exist or you do not have permission.

if I make the account SysAdmin then it works. As per http://msdn.microsoft.com/en-us/library/ms189828.aspx

{

A principal can change the password, default language, and default database for its own login.

}

There is something I am doing which is not right. Why should a Sysadmin rights be given to change password and that too my own password. Now, try to co-relate with the other websites where you change the password. You have been asked for old password… and that was it! There is a parameter in ALTER LOGIN called old_password.

USE [master]
GO
ALTER LOGIN [SQLServer-Help] WITH PASSWORD=N'pass@word123' old_password = N'Initial@Password'
GO

After learning this I realized that why there is a textbox called “Specify old password” in the Login Properties Screen (Highlighted below)

By default checkbox is unchecked and that’s why I never used it.

This brings up and interesting question. “If I know someone’s old password can I change it via logging from my account?” To test this, I created new account called FarFarAway and tried changing from SQLServer-Help login.

select ORIGINAL_LOGIN()
go
USE [master]
GO
ALTER LOGIN [FarFarAway] WITH PASSWORD=N'pass@word123' old_password = N'sa'
GO

Here is the error I received.

Msg 15151, Level 16, State 1, Line 1

Cannot alter the login ‘FarFarAway’, because it does not exist or you do not have permission.

So the answer is “No. To change someone else’s password, login should have “ALTER ANY LOGIN” permission”

Well, I didn’t know this till someone posted in SQL Bangalore User Group Facebook page. There is a lot of action there. It’s like a Mini SQL Forum. Join there for more learning!

Cheers,

Balmukund Lakhani

Twitter @blakhani

Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

Posted in Security, SQL Server User Group, SQLBangUG | Tagged: change, password, sql server | 3 Comments »

Help : I lost sa password and no one has System Administrator (SysAdmin) permission. What should I do?

Posted by blakhani on February 8, 2012

If you are thinking that I am going to show you black magic to recover sa password or other login’s password then you have hit the wrong blog post. go back to search engine and search for better tool/utility. Smile

After spending many years with SQL Server product, I have seen situations where someone wants to get in to SQL Server as system administrator as someone recently left company who had System Administrator permission (or hundred other reasons). Till SQL 2000 days, it was impossible to solve such problem other than reinstalling SQL Server. Here are the typical questions I saw in forum:

Only sysadmin user is SA and I Lost SA password.
I am locked out of SQL server i.e. that no windows users are added (or removed them) as sysadmin and I forgot the password for sa
I am windows admin. How can I get sys admin privileges on SQL server express as I removed all sysadmin accounts from SQL.

Here are the various error you might see

Unable to create new database. This is generic error which means that you are not having permission.

TITLE: Microsoft SQL Server Management Studio
——————————
Create failed for Database ‘SQLServer-Help’. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1901+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
CREATE DATABASE permission denied in database ‘master’. (Microsoft SQL Server, Error: 262)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=262&LinkId=20476
——————————
BUTTONS:
OK
——————————

When you login to Management Studio, you would see only your own account and ‘sa’ as shown below

When you attempt to change password of ‘sa’ you might see below error.

TITLE: Microsoft SQL Server Management Studio
——————————
Change password failed for Login ‘sa’. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1901+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Change+password+Login&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Cannot alter the login ‘sa’, because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=15151&LinkId=20476
——————————
BUTTONS:
OK
——————————

All error messages appear because your account is not a System Administrator of SQL Server Instance.

So, what should you do now? Answer is simple, get yourself added as System Administrator. I know, you would say “don’t you think I have tried that as I got this error!”

TITLE: Microsoft SQL Server Management Studio
——————————
Add member failed for ServerRole ‘sysadmin’. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1901+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Add+member+ServerRole&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
User does not have permission to perform this action. (Microsoft SQL Server, Error: 15247)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=15247&LinkId=20476
——————————
BUTTONS:
OK
——————————

Okay, here is the step by step guide to add any account as System Administrator of SQL Server. This is documented and completely supported way to gain back the rights. To log into SQL Server as SysAdmin, you need to have Local Administrator permission on the windows which is hosting SQL Server. If you don’t have that also then you may want to check with your windows team to get access (I am not a windows guy)

Steps to login to SQL Server as System Administrator. [Provided you are having windows local administrator permissions]

Stop the SQL Server Service using ANY of below command.
- Net Stop MSSQLServer (for default instance) / Net Step MSSQL$<InstanceName> If you want to know instance name, refer my earlier blog
- Use SQL Server Configuration manager and stop the SQL service. [Start>Programs>Microsoft SQL Server 2005>Configuration Tools>SQL Server Configuration Manager]
- Use Services console [ Start > Run > Services.msc] and locate the SQL instance you want to stop.
Start SQL Server in Single User mode. You need to use start-up parameter m to start SQL Service in single user mode. I prefer command line but its your choice.
- Using command line
  - net start MSSQLServer /m SQLCMD [For default instance]
  - net start MSSQL$<InstanceName> /m SQLCMD [For named instance]
- Using configuration Manager
  - Locate the service which you have stopped earlier. Go to its properties, “Advanced”, click on drop down at “Startup Parameters” and add ;-mSQLCMD as shown below

You might notice that I have use SQLCMD after m. That’s not a typo. Many times, when you start SQL Server in single user mode, application grabs connection before you could. SQLCMD ensures that only SQLCMD program can connect to SQL Server when its running in single use mode. Here is the error you might see if above happens. SQLCMD should be in UPPERCASE. else that would also show same error. Please make sure there is no space between “;” and “-m”, the registry parameter parser is sensitive to such typos. You should see an entry in the SQL Server ERRORLOG file that says “SQL Server started in single-user mode.”

TITLE: Connect to Server
——————————
Cannot connect to (local)\SQL2k8R2.
——————————
ADDITIONAL INFORMATION:
Login failed for user ‘Contoso\demouser’. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18461&LinkId=20476
——————————
BUTTONS:
OK
——————————

Connect to SQL Server and add desired account in SysAdmin role.
I normally prefer to do it from SQLCMD (that’s why I added SQLCMD after m)
- Open administrator command prompt. (i.e. right click on command prompt shortcut and choose “Run As Administrator”
- Type sqlcmd –S <complete instance name> For example
  - sqlcmd –S. (for default instance)
  - sqlcmd –S.\MyInstance
    For getting exact name, your my earlier blog
- You are connected as System Administrator, because you are part of local administrator group in windows.
- At this point you can add any account to sysadmin because you are connect as sysadmin. Here is the script I normally use to add local administrator group as a part of SysAdmin group in SQL Server Instance. You may want to tweak this as per your needs because I am adding all local admin as sysadmin which is not a good practise.

USE [master]
GO
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
EXEC master..sp_addsrvrolemember @loginame = N’BUILTIN\Administrators’, @rolename = N’sysadmin’
GO

Here is how it would look on sqlcmd command prompt

Stop SQL Server Service. You can use any method as described in first step
Start SQL Service normally. This means that you need to remove startup parameter

Here is the MSDN reference for above, just to show that its documented.

So, to conclude, I have not shown any trick to recover any password. Just showed you detailed steps to gain sysadmin access provided you have windows admin rights.

DISCLAIMER: Use the method that is described in this article only as a failure recovery mechanism

Posted in Configuration Manager, ERRORLOG, forgot sa password, Forgot the SA password in Sql Server 2005, sa password lost, screen shot, Screenshot, Security, SQL Server, Step by Step | 43 Comments »

Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Blog Stats

Blogroll

Select GETDATE()

Subscribe

Follow Blog via Email

Date < getdate()

Select location from world

Meta

Archive for the ‘Security’ Category

Did you know? How non-sysadmin can change their own password in SQL Server?

Help : I lost sa password and no one has System Administrator (SysAdmin) permission. What should I do?