Home All Groups Group Topic Archive Search About

setup a sql login which can only reset password

microsoft.public.sqlserver.server


Author
23 Jun 2009 1:55 PM
sarah
Hi,

I'm using sql2005. I want to setup a sql login for our helpdesk to reset sql
login password only.

Please advice,

Thanks
Sarah

Author
23 Jun 2009 3:13 PM
Tibor Karaszi
Did you try GRANT ALTER ANY LOGIN TO loginName?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


Show quoteHide quote
"sarah" <sa***@discussions.microsoft.com> wrote in message
news:2A35FDE1-1666-4E4A-8D2F-8FC922F7FE85@microsoft.com...
> Hi,
>
> I'm using sql2005. I want to setup a sql login for our helpdesk to
> reset sql
> login password only.
>
> Please advice,
>
> Thanks
> Sarah
Are all your drivers up to date? click for free checkup

Author
23 Jun 2009 3:53 PM
sarah
Hi Tibor,

I tried and it gives the helpdesk the same error saying
"Cannot alter login ' xxx', because it doesn't exist or you do not hav
permission"

I don't understand. This account has security admin right as well.

any idea?

Thanks
Sarah

Show quoteHide quote
"Tibor Karaszi" wrote:

> Did you try GRANT ALTER ANY LOGIN TO loginName?
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "sarah" <sa***@discussions.microsoft.com> wrote in message
> news:2A35FDE1-1666-4E4A-8D2F-8FC922F7FE85@microsoft.com...
> > Hi,
> >
> > I'm using sql2005. I want to setup a sql login for our helpdesk to
> > reset sql
> > login password only.
> >
> > Please advice,
> >
> > Thanks
> > Sarah
>
>
Author
23 Jun 2009 7:21 PM
Tibor Karaszi
Hmm, just as a test, does it work if you provide the old password? (I
know it isn't what you are looking for, but just as a troubleshooting
measure...)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


Show quoteHide quote
"sarah" <sa***@discussions.microsoft.com> wrote in message
news:264F1183-8448-41D6-8A95-2564A1752CB2@microsoft.com...
> Hi Tibor,
>
> I tried and it gives the helpdesk the same error saying
> "Cannot alter login ' xxx', because it doesn't exist or you do not
> hav
> permission"
>
> I don't understand. This account has security admin right as well.
>
> any idea?
>
> Thanks
> Sarah
>
> "Tibor Karaszi" wrote:
>
>> Did you try GRANT ALTER ANY LOGIN TO loginName?
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>>
>> "sarah" <sa***@discussions.microsoft.com> wrote in message
>> news:2A35FDE1-1666-4E4A-8D2F-8FC922F7FE85@microsoft.com...
>> > Hi,
>> >
>> > I'm using sql2005. I want to setup a sql login for our helpdesk
>> > to
>> > reset sql
>> > login password only.
>> >
>> > Please advice,
>> >
>> > Thanks
>> > Sarah
>>
>>
Author
24 Jun 2009 3:22 PM
sarah
Do you mean that I reset with the old password?


Show quoteHide quote
"Tibor Karaszi" wrote:

> Hmm, just as a test, does it work if you provide the old password? (I
> know it isn't what you are looking for, but just as a troubleshooting
> measure...)
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "sarah" <sa***@discussions.microsoft.com> wrote in message
> news:264F1183-8448-41D6-8A95-2564A1752CB2@microsoft.com...
> > Hi Tibor,
> >
> > I tried and it gives the helpdesk the same error saying
> > "Cannot alter login ' xxx', because it doesn't exist or you do not
> > hav
> > permission"
> >
> > I don't understand. This account has security admin right as well.
> >
> > any idea?
> >
> > Thanks
> > Sarah
> >
> > "Tibor Karaszi" wrote:
> >
> >> Did you try GRANT ALTER ANY LOGIN TO loginName?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "sarah" <sa***@discussions.microsoft.com> wrote in message
> >> news:2A35FDE1-1666-4E4A-8D2F-8FC922F7FE85@microsoft.com...
> >> > Hi,
> >> >
> >> > I'm using sql2005. I want to setup a sql login for our helpdesk
> >> > to
> >> > reset sql
> >> > login password only.
> >> >
> >> > Please advice,
> >> >
> >> > Thanks
> >> > Sarah
> >>
> >>
>
>
Author
24 Jun 2009 3:32 PM
Tibor Karaszi
My thinking is that you might have to specify the old password when
changing password, if you have ALTER ANY LOGIN permissions. I just
want to confirm whether this is the case and if you see that same
behavior.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


Show quoteHide quote
"sarah" <sa***@discussions.microsoft.com> wrote in message
news:676B74A3-E230-474D-A782-0689369E6D30@microsoft.com...
> Do you mean that I reset with the old password?
>
>
> "Tibor Karaszi" wrote:
>
>> Hmm, just as a test, does it work if you provide the old password?
>> (I
>> know it isn't what you are looking for, but just as a
>> troubleshooting
>> measure...)
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>>
>> "sarah" <sa***@discussions.microsoft.com> wrote in message
>> news:264F1183-8448-41D6-8A95-2564A1752CB2@microsoft.com...
>> > Hi Tibor,
>> >
>> > I tried and it gives the helpdesk the same error saying
>> > "Cannot alter login ' xxx', because it doesn't exist or you do
>> > not
>> > hav
>> > permission"
>> >
>> > I don't understand. This account has security admin right as
>> > well.
>> >
>> > any idea?
>> >
>> > Thanks
>> > Sarah
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Did you try GRANT ALTER ANY LOGIN TO loginName?
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "sarah" <sa***@discussions.microsoft.com> wrote in message
>> >> news:2A35FDE1-1666-4E4A-8D2F-8FC922F7FE85@microsoft.com...
>> >> > Hi,
>> >> >
>> >> > I'm using sql2005. I want to setup a sql login for our
>> >> > helpdesk
>> >> > to
>> >> > reset sql
>> >> > login password only.
>> >> >
>> >> > Please advice,
>> >> >
>> >> > Thanks
>> >> > Sarah
>> >>
>> >>
>>
>>
Author
25 Jun 2009 12:58 PM
sarah
Hi Tibor,

The problem is that I wouldn't be able to know the old password when i'm
doing the reset.
I couldn't test it right now, because if I login as helpdesk, i reset the
password but not other helpdesk people. I guess it might be because I have
sysadmin permission with my windows authentication.

How do you think?

thanks
Sarah

Show quoteHide quote
"Tibor Karaszi" wrote:

> My thinking is that you might have to specify the old password when
> changing password, if you have ALTER ANY LOGIN permissions. I just
> want to confirm whether this is the case and if you see that same
> behavior.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "sarah" <sa***@discussions.microsoft.com> wrote in message
> news:676B74A3-E230-474D-A782-0689369E6D30@microsoft.com...
> > Do you mean that I reset with the old password?
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Hmm, just as a test, does it work if you provide the old password?
> >> (I
> >> know it isn't what you are looking for, but just as a
> >> troubleshooting
> >> measure...)
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "sarah" <sa***@discussions.microsoft.com> wrote in message
> >> news:264F1183-8448-41D6-8A95-2564A1752CB2@microsoft.com...
> >> > Hi Tibor,
> >> >
> >> > I tried and it gives the helpdesk the same error saying
> >> > "Cannot alter login ' xxx', because it doesn't exist or you do
> >> > not
> >> > hav
> >> > permission"
> >> >
> >> > I don't understand. This account has security admin right as
> >> > well.
> >> >
> >> > any idea?
> >> >
> >> > Thanks
> >> > Sarah
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Did you try GRANT ALTER ANY LOGIN TO loginName?
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "sarah" <sa***@discussions.microsoft.com> wrote in message
> >> >> news:2A35FDE1-1666-4E4A-8D2F-8FC922F7FE85@microsoft.com...
> >> >> > Hi,
> >> >> >
> >> >> > I'm using sql2005. I want to setup a sql login for our
> >> >> > helpdesk
> >> >> > to
> >> >> > reset sql
> >> >> > login password only.
> >> >> >
> >> >> > Please advice,
> >> >> >
> >> >> > Thanks
> >> >> > Sarah
> >> >>
> >> >>
> >>
> >>
>
>
Author
25 Jun 2009 3:31 PM
Tibor Karaszi
Hi Sarah,

> The problem is that I wouldn't be able to know the old password when
> i'm
> doing the reset.

I understand that. My suggestion was only part of the troubleshooting
process, not meant as a solution. My tests (I just did) do indicate
that you are able to change pwd for a login if you have ALTER ANY
LOGIN. See below TSQL script. Perhaps you can test ttat script and see
if you get the same result?
I also managed to change pwd from SSMS, Object Explorer.
I'm on SQL 2008, sp1, btw.

CREATE LOGIN KAlle WITH PASSWORD = 'a'
CREATE LOGIN Olle WITH PASSWORD = 'a'

RAISERROR('Test without ALTER permissions', 10, 1) WITH NOWAIT
EXECUTE AS LOGIN = 'Olle'
ALTER LOGIN Kalle WITH PASSWORD = 'b' --Error
REVERT
GO

RAISERROR(' ', 10, 1) WITH NOWAIT

GRANT ALTER ANY LOGIN TO Olle
RAISERROR('Test with ALTER permissions', 10, 1) WITH NOWAIT
EXECUTE AS LOGIN = 'Olle'
ALTER LOGIN Kalle WITH PASSWORD = 'b' --No error
REVERT

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


Show quoteHide quote
"sarah" <sa***@discussions.microsoft.com> wrote in message
news:C755BEAD-4979-4A3F-B102-DADEBB57A142@microsoft.com...
> Hi Tibor,
>
> The problem is that I wouldn't be able to know the old password when
> i'm
> doing the reset.
> I couldn't test it right now, because if I login as helpdesk, i
> reset the
> password but not other helpdesk people. I guess it might be because
> I have
> sysadmin permission with my windows authentication.
>
> How do you think?
>
> thanks
> Sarah
>
> "Tibor Karaszi" wrote:
>
>> My thinking is that you might have to specify the old password when
>> changing password, if you have ALTER ANY LOGIN permissions. I just
>> want to confirm whether this is the case and if you see that same
>> behavior.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>>
>> "sarah" <sa***@discussions.microsoft.com> wrote in message
>> news:676B74A3-E230-474D-A782-0689369E6D30@microsoft.com...
>> > Do you mean that I reset with the old password?
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Hmm, just as a test, does it work if you provide the old
>> >> password?
>> >> (I
>> >> know it isn't what you are looking for, but just as a
>> >> troubleshooting
>> >> measure...)
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "sarah" <sa***@discussions.microsoft.com> wrote in message
>> >> news:264F1183-8448-41D6-8A95-2564A1752CB2@microsoft.com...
>> >> > Hi Tibor,
>> >> >
>> >> > I tried and it gives the helpdesk the same error saying
>> >> > "Cannot alter login ' xxx', because it doesn't exist or you do
>> >> > not
>> >> > hav
>> >> > permission"
>> >> >
>> >> > I don't understand. This account has security admin right as
>> >> > well.
>> >> >
>> >> > any idea?
>> >> >
>> >> > Thanks
>> >> > Sarah
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> Did you try GRANT ALTER ANY LOGIN TO loginName?
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://sqlblog.com/blogs/tibor_karaszi
>> >> >>
>> >> >>
>> >> >> "sarah" <sa***@discussions.microsoft.com> wrote in message
>> >> >> news:2A35FDE1-1666-4E4A-8D2F-8FC922F7FE85@microsoft.com...
>> >> >> > Hi,
>> >> >> >
>> >> >> > I'm using sql2005. I want to setup a sql login for our
>> >> >> > helpdesk
>> >> >> > to
>> >> >> > reset sql
>> >> >> > login password only.
>> >> >> >
>> >> >> > Please advice,
>> >> >> >
>> >> >> > Thanks
>> >> >> > Sarah
>> >> >>
>> >> >>
>> >>
>> >>
>>
>>

Bookmark and Share

Post Other interesting topics
Multiple databases performance
Can I script out SQL Server jobs programmatically?
Sql transaction log size because of reindexing
User login date
kill the long run job automatically
collation for multiple language in a column
Identifying Memory Pressures
Jobs cannot running
understanding metrics/performance
SQL Server Import Export Wizard does not create table indexes