sp_rename - Permission denied problem

[LucieLastic]

hi All

This is probably an easy one to fix. The username has the following privileges:

public
db_dlladmin
db_datareader
db_datawriter

From reading BoL, I thought the db_dlladmin privilege would allow sp_rename to be executed by this user, do I need db_owner as well (I didn't really want to give this privilege)?

thanks in advance
Lou

 

[SQLDenis]

Are you renaming tables/[rocedures or a database?

Members of the sysadmin fixed server role, the db_owner and db_ddladmin fixed database roles, or the owner of the object can execute sp_rename.

Only members of the sysadmin and dbcreator fixed server roles can execute sp_rename with 'database' as the object_type.

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[LucieLastic]

hi

I'm renaming two tables.

Lou

 

[LucieLastic]

I think the problem is that the sp (containing sp_rename) was created by sa and the calling username was of 'lower importance'. Creating the sp with the normal username works. Now I think about it, it's obvious why it wasn't working.

Lou