Permissions problem with sp_rename for db_owner when renaming synonyms

• GuitarGuy

  SSC Veteran

  Points: 285

  More actions

  December 21, 2009 at 7:39 pm

  #217842

  Hi All,

  I'm having problems when executing sp_rename on a synonym. The user is a member of the db_owner role and is the same user that has created the synonym in the first place.

  If I run this...

  create table dbo.test_table

  (test_id int identity(1, 1) not null primary key

  ,col1 varchar(100) null

  )

  create synonym dbo.test_syn for dbo.test_table

  select * from dbo.test_syn

  go

  exec sp_rename 'dbo.test_syn', 'test_syn_renamed'

  I get this...

  Msg 297, Level 16, State 3, Procedure sp_rename, Line 282

  The user does not have permission to perform this action.

  Does anybody have any idea on what I could be doing wrong? If I do this as sysadmin, it works fine but using sysadmin for live situations is a last resort that I'd rather not have to go to.

  Cheers,

  Tim