Access to sp_password ???
-
HI,
MMSQL 2000/sp3
How does a user have permission to run the sp_password? I don't see it in the user database. I see it in the master database. And when I look at the master db role public , I only see 2 users "dbo" (sa) and "guest". How does a user ie: user1, located in another user database, able to run this sp if not granted access to the master db and is not a member of the master db role public ??
Many thanks. Jeff
-
public
-
they use the guest account that is part of the public role and can not (should not
) be removed!HTH
* Noel -
Long-winded answer:
sp_password is a system stored procedure. Because it starts with sp_, SQL Server will look for it in the master database.
The master database has the guest user activated (this is required, BTW). If you don't have specific user credentials for a database and the guest user is activated, that's what you gain rights as. As stated previously, the guest user is a member of public (all users are members of public). Public has rights to execute the stored procedure. After all, if you have a SQL Server login and you want to change the password, you have to use sp_password to do so.
There is logic inside sp_password to verify you are at least a securityadmin role member to change a normal password that isn't your own and at least a sysadmin role member to change a password belonging to a login that is a member of the sysadmin role.
K. Brian Kelley
@kbriankelley -
Hi Brian, any thought as to why MS does this and why in the master database, and not a user database? ie: why not place these types of sp's in the model db and allow the DBA to explicitly grant permissions (via a user or user db public role)? Or are these sp's that are granted to the master db's public role, considered relatively harmless?
Any thought of adding this to a PART 2 of "SQL Server 2000: Permissions on System Tables Granted to Logins Due to the Public Role"? I really enjoyed this whitepaper ...
Many thanks. Jeff
-
It makes sense to keep all system level stored procedures in only one place. Hence the reason for master. Consider that if you placed it in model, that means for every database that gets generated, that stored procedure gets recreated. That includes tempdb, which is generated from model every time SQL Server is restarted. Also, remember that the tables handling logins/passwords are in master. You'd still have to have some way of getting back, even if the stored procedure resided in a user database. In any case, stored procedures like sp_password are necessary if password management is needed. Keep in mind in SQL 2005 you'll be able to set password expiration on SQL Server logins based on whatever the machine's working system policy says (local + group policies). We can't enforce password expiration now without creating a custom (and unsupported process) but at least a user who has to log in to a SQL Server using a SQL Server account can change the password.
I wouldn't go so far to say all the sp's granted to the master db's public role are relatively harmless. The sp_help ones allow for a great deal of recon by a potential attacker if that attacker manages to compromise a login.
As for a second whitepaper, I've actually been strapped for time writing as it is. Child on the way (due this month) plus leading an Active Directory migration has sucked up almost all of my time. I need to spin out a few articles I've had in the cooker for a few months now. No time this weekend, though, unless I choose not to get any sleep.
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply