Re: Tie down developer security
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Tue, 23 Jan 2007 23:12:56 +0000 (UTC)
thejamie (thejamie@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
Our system runs production on 2000 and OLAP on 2005. I have just
glanced through Erland Sommarskog text for understanding schemas and
question that it may be possible to stop the user from altering
dbo.spMyProcedure but still enabling them to create
myuserlogon.spMyProcedure for testing purposes. This may not be worded
correctly - this works on tables... as in dbo.table1 and
myuserlong.table1.
GRANT CREATE PROCEDURE TO user1
should cut it. Note that user1 should not be added to any prededined
role.
By the way, there is yet one alternative for the really evil sysadmin:
the developer could be left to stick with temporary stored procedures:
CREATE PROCEDURE #mytempproc ... AS
If possible, is there a way to seed the permission process on 2000 so
that when the permissions are carried across to SQL 2005, very little
maintenence will need to occur to sync up restored databases from 2000
on the 2005 server?
I haven't tested what happens, but if you restore the database on
SQL 2005, everything should be like before. The user has permissions
to create procedures, but he only has access to the schema he owns
himself.
--
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
.
- Prev by Date: Re: Can't access linked server, except from
- Next by Date: Trouble with linked servers
- Previous by thread: user security
- Next by thread: Trouble with linked servers
- Index(es):
Relevant Pages
|
|
