November 28, 2005 at 10:50 am
I was playing about with the sp_password procedure and changed it to enforce minimum password lengths etc.. After some playing about I changed it back to it's original form. Now the non sysadmin users can't change passwords. They get errors saying "guest user does not have permissions to execute DBCC auditevent"
sp_password was changed with an ALTER PROCEDURE command under a sysadmin account. In EM it is now listed as a User stored procedure rather than a system one. I've messed up some permissions somewhere but can't seem to fix it. Guest is still a member of public and public still has execute permissions on sp_password.
How do I check if guest has DBCC auditevent permissions and how did I manage to remove them?!
cheers
November 28, 2005 at 10:56 am
You were probably logged in as yourself and not SA. That means the procedure became YOURS. This is a prime example of why system stored procedures shouldn't be changed. You should have copied it and given it a new name, then 'played' with it.
You might try logging in as SA and making a change to the procedure and then changing it back again. As far as I know, there isn't a command to change the owner.
-SQLBill
November 28, 2005 at 11:29 am
You can change the type back to system from user by using an undocumented procedure called sp_MS_ marksystemobject. Permissions will then default to members of the sysadmin server role, the db_owner database role and the database owner and are not transferable.
November 29, 2005 at 2:16 am
Thanks to you both - the sp_MS_marksystemobject procedure fixed it.
We have a new security analyst in our company who's concerned about the lack of complexity on our SQL passwords. Although normally I would agree that changing system stored procedures is not a good idea, do you think in this case the benefits outweigh this? Changing to NT authetication or upgrading to 2005 are not options at this this stage.
cheers
November 29, 2005 at 11:49 am
It depends on why you are changing the procedure. I have changed some system procedures, specifically sp_spaceused. Spaceused returns more data than I normally need, so I changed it. However, what I really did was copy it as a new procedure called usp_spaceused, then made the changes I needed. I then run the usp_spaceused procedure daily and sp_spaceused still exists in its original state.
-SQLBill
November 29, 2005 at 4:02 pm
Ultimately this is a business decision your organization is going to have to make. It's not hard to modify the stored procedure and have it refer to a second stored procedure which checks complexity, but changing the system stored procedure isn't going to be something anyone is likely to recommend because that means giving you advice that would potentially put your SQL Server in an unsupported state.
And that's the main risk: Microsoft will not support you if there is an issue. Since security is really about business risk management, that needs to be taken into account. A second risk you take is that if Microsoft were to change sp_password through a service pack or patch, you'll have to rework your solution.
K. Brian Kelley
@kbriankelley
November 30, 2005 at 4:25 pm
Hi,
I had (have and will have) the similar case. With the same DBCC Auditevent error messages. I had (have and will have) to change sp_adduser that a third-party industry leader CRM application is using in the code to add users through the application. We don't have access to the third-party code and sp_adduser allows only DBO (sa) to add users. The recommened procedure for adding users is sp_grantdbaccess which recognizes database roles but the application uses sp_adduser.
The vendor's solution is to modify sp_adduser to allow users other then SA to add users which is requred for the enterprise solutions. I did not have problems in SQL Server 7, but in 2000 I had to do the same thing as Beath did. I did use SA connection when modifying the procedure. I do have DBO as an owner. The modified procedure is marked as USER type. I did not change the type using sp_MS_ marksystemobject, so I may do it in the future. But what I did is comment out the dbcc auditevent as follows:
begin
-- AUDIT SUCCESSFUL SECURITY CHECK --
-- I excluded dbcc auditevent, could not make it work for non-sysadmins
-- dbcc auditevent (109, 1, 1, @loginame, @name_in_db, @grpname , NULL)
-- The next statement only to have any executable line between begin and end for syntax
select @ret=@ret
end
Regards,Yelena Varsha
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply