[prev in list] [next in list] [prev in thread] [next in thread] 

List:       freetds
Subject:    [freetds] OT: sp_rename broken for non-dbo on Microsoft servers
From:       "Lowden, James K" <LowdenJK () bernstein ! com>
Date:       2004-04-21 20:42:07
Message-ID: 04C5B956C56BE144A4A1BFA26732E5490247070A () ac2knjs0106 ! ac ! lp ! acml ! com
[Download RAW message or body]

I'd like to start a rumor or confirm one.  

On our SQL Server 7.0 box, users can create their own tables.  If I create a table:

	create table a (a int)

when logged in as me, it creates an object "[dbname].LowdenJK.a".  Fine and good.  \
Then, I try to rename the column:

	sp_rename 'LowdenJK.a.a', b

Whatever form of sp_rename I use, I get the error:

Msg 15334, Level 11, State 1, Server NTS0198, Procedure sp_rename, Line 300
Error: Only members of the sysadmin role or the database owner can rename items they \
do not own.

even though it's completely absolutely clear I in fact do own that object.  

The culprit appears to be line 310 of sp_rename, near the bottom, where we find:

if (@objtype = 'userdatatype')
	select @ownerid = uid from systypes where xusertype = @xusertype
else
	select @ownerid = ObjectProperty(@objid, 'ownerid')
if (	(not (1 = is_member('db_owner')))
	AND (not (1 = is_member('db_ddladmin')))
	AND (not (1 = is_member(user_name(@ownerid)))) )
begin
	raiserror(15334,-1,-1)
	return 1
end

The proc gets the ower of the datatype or object, as required.  It then attempts to \
decide if the owner is eligible to rename the thing.  It checks if the user belongs \
to db_owner or db_ddladmin, which is fine.  The final check, though is just bogus.  \
From the documentation, this is an incorrect use of is_member, whose parameter takes \
either a role or a group.  A user is not a role or a group, afaik.  

I think the right final test is:

	AND @ownerid <> user_id()

I tested this on our server and it seems to work.  Anyone else care to try to \
reproduce the error, confirm the bug or the fix?  I'd be much obliged.  :-)

--jkl


-----------------------------------------
The information contained in this transmission may contain privileged and \
confidential information and is intended only for the use of the person(s) named \
above. If you are not the intended recipient, or an employee or agent responsible for \
delivering this message to the intended recipient, any review, dissemination, \
distribution or duplication of this communication is strictly prohibited. If you are \
not the intended recipient, please contact the sender immediately by reply e-mail and \
destroy all copies of the original message. Please note that we do not accept account \
orders and/or instructions by e-mail, and therefore will not be responsible for \
carrying out such orders and/or instructions. If you, as the intended recipient of \
this message, the purpose of which is to inform and update our clients, prospects and \
consultants of developments relating to our services and products, would not like to \
receive further e-mail correspondence from the sender, please "reply" to the sender \
indicating your wishes.  In the U.S.: 1345 Avenue of the Americas, New York, NY \
10105.


_______________________________________________
FreeTDS mailing list
FreeTDS@lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds


[prev in list] [next in list] [prev in thread] [next in thread]