SQL Server 2005 / securityadmin role / SSMS

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.SqlServer.Smo)

Additional information:
An exception occurred while executing a Transact-SQL statement or
batch.
(Microsoft.SqlServer.ConnectionInfo)
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?

Re: SQL Server 2005 / securityadmin role / SSMS

Larry Bertolini (bertolini.1@osu.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.SqlServer.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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: SQL Server 2005 / securityadmin role / SSMS

On Tue, 8 May 2007 22:16:48 +0000 (UTC), Erland Sommarskog
wrote:


>
>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.

Re: SQL Server 2005 / securityadmin role / SSMS

Larry Bertolini (bertolini.1@osu.edu) writes:
> On Tue, 8 May 2007 22:16:48 +0000 (UTC), Erland Sommarskog
> wrote:
>>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.

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/ViewFeedbac k.aspx?FeedbackID=276639

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


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx