Re: ddl admin privilage
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Tue, 30 Oct 2007 22:36:31 +0000 (UTC)
MO (MO@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I have an existing production archive/copyback process. Using 'sa'
authority, it archives records from one db to another archive server. In
some cases buisness can request us to "copyback" some of the archived
records from archive server. Tables have identity columns on it. I set
the identity_insert on and then do this copyback funtion using 'sa' id.
In order to set the identity property, one needs ddl_admin or sysadmin
priv.
Problem is I need to transfer this whole process to a developer who
does not have sa authrity or ddl_admin rights. I don't want to give
those rights to him. How can I accomplish this w/o compromising on
security ?
The obvious suggestion is that you should change the table so that it
does not use IDENTITY, but you would generate your surrogate keys on
your own. The one case where this is not recommendable is when you
have a high insertion rate from multiple sources. In this case, rolling
your own leads easily leads to contention.
If you are on SQL 2005 you can put the process into a procedure that you
sign with a certificate. I have an article on my web site that discusses
this in detail: http://www.sommarskog.se/grantperm.html.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- ddl admin privilage
- From: MO
- ddl admin privilage
- Prev by Date: Re: linked server issue
- Next by Date: Re: Best practice for SQL Admins
- Previous by thread: ddl admin privilage
- Next by thread: Re: linked server issue
- Index(es):
Relevant Pages
|
|
