SQL Server 2005 / securityadmin role / SSMS

[Larry Bertolini]

Experiencing a little weirdness with SQL Server Management Studio...

Have created a login, that has fixed server role "securityadmin" .
The login can create/drop logins, add/remove them as users in
databases, etc.

The login can change another login's password, using SQL
from a query window (e.g. alter login bob with password='abc')

However, the login *cannot* change another login's password,
using SSMS GUI (e.g. "Security", "Logins", double-click "Bob",
change password, say "OK"). The error text is:

Change password failed for Login 'Bob'. (Microsoft.SqlS erver.Smo)

Additional information:
An exception occurred while executing a Transact-SQL statement or
batch.
(Microsoft.SqlS erver.Connectio nInfo)
Cannot alter the login 'Bob', because it does not exist or you do
not have permission.
(Microsoft SQL Server, Error: 15151)

Has anybody else experienced this?

[Erland Sommarskog]

Re: SQL Server 2005 / securityadmin role / SSMS

Larry Bertolini (bertolini.1@os u.edu) writes:

Have created a login, that has fixed server role "securityadmin" .
The login can create/drop logins, add/remove them as users in
databases, etc.
>
The login can change another login's password, using SQL
from a query window (e.g. alter login bob with password='abc')
>
However, the login *cannot* change another login's password,
using SSMS GUI (e.g. "Security", "Logins", double-click "Bob",
change password, say "OK"). The error text is:
>
Change password failed for Login 'Bob'. (Microsoft.SqlS erver.Smo)

A great thing with Mgmt Studio is that you can always script actions.
Press the Script button, and you can get a query window with the
T-SQL command that Mgmt Studio would generate if you were to press OK.

In this case you will see that the generated command includes the
OLD_PASSWORD clause. And if you supply the correct old password
for the user, it works.

I'm a little puzzled, because Books Online says:

If the login is a member of the sysadmin fixed server role or a grantee
of CONTROL SERVER permission, also requires CONTROL SERVER permission
when making the following changes:

o Resetting the password without supplying the old password.

But very apparently ALTER ANY LOGIN (which is what securityadmin maps to)
is sufficient to change the password.

So it seems that Books Online is wrong, but the Tools group were naïve
enough to believe it.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Larry Bertolini]

Re: SQL Server 2005 / securityadmin role / SSMS

On Tue, 8 May 2007 22:16:48 +0000 (UTC), Erland Sommarskog
<esquel@sommars kog.sewrote:

>
>In this case you will see that the generated command includes the
>OLD_PASSWORD clause. And if you supply the correct old password
>for the user, it works.

That's kind of an odd design decision, IMO, considering that
"user forgot password" is probably the most common reason
that a securityadmin would change a password; at that point,
nobody knows the old password.

Oh, well. Thanks for the tip about the "Script" button.

[Erland Sommarskog]

Re: SQL Server 2005 / securityadmin role / SSMS

Larry Bertolini (bertolini.1@os u.edu) writes:

On Tue, 8 May 2007 22:16:48 +0000 (UTC), Erland Sommarskog
><esquel@sommar skog.sewrote:

>>In this case you will see that the generated command includes the
>>OLD_PASSWOR D clause. And if you supply the correct old password
>>for the user, it works.

>
That's kind of an odd design decision, IMO, considering that
"user forgot password" is probably the most common reason
that a securityadmin would change a password; at that point,
nobody knows the old password.

Good point. I was a little uncertain what to file a bug for, but you are
right. What's the point with being permitted to change logins, if you
cannot help people who have forgotten their password. I filed a bug
at

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=276639

and I also sent in feedback on the topic in Books Online.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx