Authorisation to append field to a table

Question

Post reply

Authorisation to append field to a table

Andrew Page-218373

Old Hand

Points: 320
More actions
August 9, 2007 at 2:45 am

#174777

I have a web application that logs into SQL 2000 database as a single user set up as SQL Server Authentication. The user has resticted access as to what it can and can't do.
One of the tasks that needs to be done when a specific stored procedure is executed is to append a column to a table which requires higher security than the user has.
I have tried using the sp_start_job to start a job that will execute the command but the user has to be the owner and if it is the owner it can't perform the task because the owner does not have the authorisation level to do this!
Any one got any ideas as to how this could be done?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Clive Strong SSChampion Points: 11222 More actions · Answer 1

I'm assuming you don't want the user to be a member of ddl_admin?

Andrew Page-218373 Old Hand Points: 320 More actions · Answer 2

Ideally I just want them to access a single stored procedure which would give the limited functionality required.

Clive Strong SSChampion Points: 11222 More actions · Answer 3

To alter a table you need to be the table owner, sysadmin, dbo or ddl_admin, so if you want it all done in that 1 stored procedure, you will have to extend the permissions.

Also, one question...How often is this stored procedure called and will it always append one column to the table? I'm asking because you could end up with an extremely wide table.

Andrew Page-218373 Old Hand Points: 320 More actions · Answer 4

The table is used to store each level of a hierarchy in a particular transaction to make it easy for reporting. Whenever a new level(/depth) to the hierarchy is created a new column needs to be added to the table. This shouldn't happen very often once the system is set up.

I suppose the another way round it is to run a job overnight to put any extra columns in the table using a user that has the relevant permissions.

Andrew

Clive Strong SSChampion Points: 11222 More actions · Answer 5

I was going to suggest something similar. A job could be run every hour or so to add any columns. You could create some kind of work_pending table and use that as the source for which to create the new columns.

Andrew Page-218373 Old Hand Points: 320 More actions · Answer 6

Andrew Page-218373

Old Hand

Points: 320

August 9, 2007 at 7:47 am

#725717

Clive,

Thanks for your help

rgds

Andrew