granting create proc / func to create dbo objects

Question

granting create proc / func to create dbo objects

Viewing 3 posts - 1 through 4 (of 4 total)

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

Steve Jones - SSC Editor SSC Guru Points: 739541 More actions · Answer 1

If you are doing this in a dev world, two ways:

1. Let them create objects and then "mark" them for dbo status. They do this by dropping the object name in a table you set up for them. Then you ahve a job that "renames" the table to have dbo as the owner (sp_changeobjectowner) . Run the job every minute, hour, whatever.

2. Let htem put schema/code in a table. Have a job that runs as dbo and "executes" the code. Be sure you trust the developers on this one.

I prefer the first.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 2

To create objects that will be owned by a different user, the database role db_ddladmin must be granted. To restrict what objects can be created, you may deny a statement privilege.

Here is some example SQL:

exec sp_addrolemember @rolename = 'db_ddladmin', @membername = 'LocalUser'

go

deny create table to LocalUser

go

SQL = Scarcely Qualifies as a Language