How do I setup Ad hoc updates to th

[YL5956]

How do I setup Ad hoc updates to the system catalogs? Thanks.

 

[MaffewW]

What exactly are you trying to update or find out? Updates/inserts to system tables are likely to cause problems and arent supported.

 

[JamesLean]

There is an option you can set which allows direct querying of system tables. However doing this is NOT advisable - you should use system stored procedures to access system tables. --James

 

[YL5956]

Here is the sp that I'm trying to add. It allows other users to change passwords. I can't seem to get this in. Thanks.

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO


create procedure sp_dba_to_allow_non_sa_to_change_password
@old sysname = NULL, -- the old (current) password
@new sysname, -- the new password
@loginame sysname = NULL -- user to change password on
as
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
declare @self int
select @self = CASE
WHEN (@loginame is null) THEN 1
ELSE 0
END

-- CHECK PERMISSIONS --
IF (not is_srvrolemember('securityadmin') = 1)
AND not @self = 1
begin
raiserror(15210,-1,-1)
return (1)
end

-- DISALLOW USER TRANSACTION --
set implicit_transactions off
IF (@@trancount > 0)
begin
raiserror(15002,-1,-1,'sp_dba_to_allow_non_sa_to_change_password')
return (1)
end

-- RESOLVE LOGIN NAME (disallows nt names)
if @loginame is null
select @loginame = suser_sname()
if not exists (select * from master.dbo.syslogins where
loginname = @loginame and isntname = 0)
begin
raiserror(15007,-1,-1,@loginame)
return (1)
end

-- DISALLOW SA PASSWORD TO BE CHANGED
if @loginame = 'sa'
begin
-- raiserror(21050,-1,-1)
raiserror('The sa password cannot be changed.',16,1)
return (1)
end

-- CHECK OLD PASSWORD IF NEEDED --
if (@self = 1 or @old is not null)
if not exists (select * from master.dbo.sysxlogins
where srvid IS NULL and
name = @loginame and
( (@old is null and password is null) or
(pwdcompare(@old, password, (CASE WHEN xstatus&2048 = 2048 THEN 1 ELSE 0 END)) = 1) ) )
begin
raiserror(15211,-1,-1)
return (1)
end

-- CHANGE THE PASSWORD --
update master.dbo.sysxlogins
set password = convert(varbinary(256), pwdencrypt(@new)), xdate2 = getdate(), xstatus = xstatus & (~2048)
where name = @loginame and srvid IS NULL

-- FINALIZATION: RETURN SUCCESS/FAILURE --
if @@error <> 0
return (1)
raiserror(15478,-1,-1)
return (0) -- sp_dba_to_allow_non_sa_to_change_password





GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO

 

[JamesLean]

That's the script for the sp_password system stored procedure!!
Why are you trying to duplicate an SP that already exists?! --James

 

[tlbroadbent]

I strongly advise against allowing other users to change passwords in this manner. Add the trusted users to the securityadmin fixed server role and then those users can run the system stored procedure sp_password. This is a much safer route than allowing adhoc changes to system tables. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[YL5956]

This SP is being used by one of our webapps. I'm not sure why they didn't use the SP_Password instead. The developer decided to create this SP just for the web users. Please let me know how I can create this SP in the database. Thanks.

 

[tlbroadbent]

You can set the server option, &quot;Allow Updates&quot; but this allows system updates to all system tables on the server. This option should not be left on. Only the system or server administrator can turn the option on.

In other words, what you have been instructed to do is not possible without leaving the server extemely vulnerable to unauthorized changes.

All users can use the existing sp_password to change their own password. If you want User(s) to have the ability to change other user passowrds try one of the following scenarios.

1) Add these users to the securityadmin role as noted previously.

2) Create a stored procedure that inserts password changes into a transaction table and notifies the system and server administrators. They could then review the change and implement it if acceptable.

3) Create a stored procedure that inserts password changes into a transaction table and starts a job that runs with system administrator privileges to make the actual password change. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[YL5956]

Thanks you for the assistance.

So to go over the steps would I perform the following sequence of steps?

1) exec sp_configure 'allow updates' '1',

2) Create the SP through Query Analyzer

3) set allow update back to 0

 

[YL5956]

Hi,

I ran the exec sp_configure 'allow updates' '1', and restarted the service but the system is still not allowing me to perform the ad hoc query. Please help. Thanks.

 

[tlbroadbent]

Like I said, you have to leave the option on when executing the stored procedure not just when creating the SP. Basically, that means you must leave it on all the time. That's not a good practice.

Do you and the developer know that every user can use sp_password to change his or her own password?

You can do things the right way or the developer's way. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[YL5956]

Hi Terry,

Here is the situation. I'm sitting up a development server based on our production image. I am not the DBA for the Production machines and they are reluctant to change the machine configuration. The only way I can be sure that the my server is identical to production is to mirror it. This would include the SP in question. I notice that our production machine does have 'allow updates' set to zero so I'm not sure how the DBA team was able to get it in. I don't mean to be difficult and I do understand your concerns but I will have to concede this issue since I'm not on the DBA team and have no say on the Production machine issues. Thank you for your assistance in all of this.

 

[JamesLean]

Through this whole thread, we haven't yet established what is actually happening (when you say &quot;you can't get it in&quot. Leaving aside the security issues for a moment, can you tell us what the actual error is? Is it not letting you create the SP in the first place or is it just failing when trying to execute it? --James

 

[YL5956]

Here is the message I'm getting from Query Analyzer after running the script to create the stored procedure.

Server: Msg 259, Level 16, State 2, Procedure sp_dba_to_allow_non_sa_to_change_password, Line 63
Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.

 

[JamesLean]

OK, then you do need to set the 'allow updates' option. The error is self-explanatory. --James

 

[YL5956]

I did that. Here is what my server shows but I still get the same error message. I'm sure I'm doing something wrong. Thanks.

name minimum maximum config_value run_value
------------- --------- ----------- ------------ ---------
allow updates 0 1 1 0

 

[JamesLean]

See the following link for more info and a How To:

http://www.msdn.microsoft.com/library/en-us/adminsql/ad_config_3kxa.asp

--James