June 18, 2008 at 3:42 am
Got a bit of a strange one, one of our lovely offshore devs are claiming that a new app willl need persmission to reseed the identity column on a table, or drop and create the table.
They want DDL_Admin for this, but I really dont want to give the service account this level of permissions.
I am not really sure of a good solution as reseed is a DBCC command, Truncate is DBO (at least it was in 2000)
I notice under database permissions you can give a User CREATE permissions, but this seems a touch problematic as I get this error
The specified schema name "dbo" either does not exist or you do not have permission to use it.
Any one got an idea to the minium permission I can give this account for said action.
(the reseed or create will be via an SP )
June 18, 2008 at 9:39 am
Try using WITH EXECUTE AS 'dbo' in the stored procedure to switch context to an user that can do TRUNCATE and DBCC CHECKIDENT. Then you just grant the user EXECUTE permission on the stored procedure and nothing else. The context reverts back when the procedure ends.
Greg
June 18, 2008 at 9:42 am
in all my years I didnt know you could do such a thing.......
is this new to 2005 ?
oh my
June 18, 2008 at 9:45 am
Yes. See "context switching" in BOL.
Greg
June 19, 2008 at 9:21 am
... hmmm ...
Granting this type of authority on a development environment seems quite reasonable. Granting this authority on a production system temporarily during the execution of a database conversion or application upgrade seems reasonable as well (stress the word 'temporary'). However if this type of authority and action are needed within a production system on a regular basis there might be some potentially serious architectural issues present.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply