Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,156,122 hits

  • Select GETDATE()

    February 2026
    M T W T F S S
     1
    2345678
    9101112131415
    16171819202122
    232425262728  

Posts Tagged ‘sql server’

« Previous Entries
Next Entries »

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)

image

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.

 

image

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: , , | 4 Comments »

    Did you know? You can generate Insert Statement with data using Management Studio!!!

    Posted by blakhani on December 13, 2013


    I must confess that at least I didn’t know about that option till I saw an internal email thread. And my first reaction was – Damn! all these years and never spotted that! Yes, we can get all of the INSERT statements for the data straight out of SSMS. Another hidden feature of SSMS.

    Earlier, I used to search on internet and get some stored procedure to get the insert statements generated for me. Here were few search strings which used to give me proper results “SQL Generate Insert Script”, “SQL Server Generate Insert Script”, "SQL Server Script Insert Statements", "Create Insert Script SQL Server", "Generate Insert Script from Table"

    Now I can avoid all those searches and do that right from the Management Studio. For demo purpose I would create a small database and show that option. Please note that I am using SQL Server 2012 Management Studio (SSMS 2012)

    CREATE DATABASE [myAppDB]
go
Use myAppDB
go
Create table HelloSQL (iID int, vFname varchar(100), vLName varchar(100))
go
insert into HelloSQL values 
(1, 'Balmukund', 'Lakhani'),
(2, 'Vinod', 'Kumar M'),
(3, 'Pinal', 'Dave'),
(4, 'Sourabh', 'Agarwal'),
(5, 'Amit', 'Banerjee')
go
Select * from HelloSQL
go

     

    To achieve our insert statements with script use Object Explorer and connect to an instance of the SQL Server. Expand Databases folder, Choose the database for which we want the script, right click, Tasks > Generate Scripts.

    Once Wizard is launched, its self-explanatory to navigate through screen.

    image

    After welcome screen, we need to choose the object (table HelloSQL in our case) for which we want script to be generated.

    image

    In next screen, we need to choose the destination of script. I have selected “Save to new query window”

    image

    In the same screen, we have a magical button called “Advanced”. That’s where we have many options about scripting.

    image

    I have selected “Schema and Data” under “Type of data to script” and hit OK. Hit Next on previous screen

    image

    hit Next and Finish.

    image

    … and we have what we needed. Schema creation as well as Insert Statement for the data in table.

    image

    There are many other options in advanced tab. Play with them and get the desired script. I have learned this and it was new to me. Hope this is useful to you also. SQL Server generate script with data is a powerful SQL Server tool in order to create SQL script to move data from one database to another database. SQL programmers can export data as sql script by using one of the existing SQL Server tools, SQL Server Generate Script Wizard.

     

    Cheers,

    Balmukund Lakhani

    Twitter @blakhani

    Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

    Posted in Screenshot, SQL Server, SQL Server Management Studio, SSMS, Step by Step | Tagged: , , , , , , , | 10 Comments »

    « Previous Entries
    Next Entries »