468,119 Members | 2,086 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,119 developers. It's quick & easy.

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?
May 8 '07 #1
3 10453
Larry Bertolini (be*********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 8 '07 #2
On Tue, 8 May 2007 22:16:48 +0000 (UTC), Erland Sommarskog
<es****@sommarskog.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.
May 9 '07 #3
Larry Bertolini (be*********@osu.edu) writes:
On Tue, 8 May 2007 22:16:48 +0000 (UTC), Erland Sommarskog
<es****@sommarskog.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.
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/SQLSer...dbackID=276639

and I also sent in feedback on the topic in Books Online.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 10 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Peter Nurse | last post: by
2 posts views Thread by kress1963nov22 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.