Unable to create procedure with ''dbo'' owner
-
Hi
I hv login a 'xyz' login with database role as public,db_datareader,db_datawriter
when 'xyz' runs following command
CREATE PROCEDURE dbo.<Procedure Name>
as
some T-SQL
it gives error as
"Specified owner name 'dbo' either does not exist or you do not have permission to use it."
I can get rid of this problem by assigning 'xyz' to db_owner database role, but i do not want that bcoz db_owner can alter the table which I don't want to happen
Is there anyother way to get rid of this problem???
Thanks & Regards
Ritz
-
The only two roles that can create an object as a different owner are: db_owner, db_ddladmin. Unfortunately, they do have permissions to alter the table due to the nature of their roles. Out of curiousity, why isn't a dbo level account running this CREATE PROCEDURE statement?
K. Brian Kelley
@kbriankelley -
Hi Kelley
thx for reply. I can't use dbo level account bcoz user with this role can alter the table and I want developers to create Store Proc and use DML commands. Also SQL Server does not allow DENY ALTER TABLE TO <ACCOUNT>
hence can not use dbo level account.. but i guess i will hv to use one of this two role to resolve this problem.. do let me know if u hv anything without using db_owner,db_ddladmin
Ritz
-
Allow them to create stored procedures as themselves. A DBA level should be doing at least a cursory review. If it looks good, execute an sp_changeobjectowner command to make it owned by dbo. This is a normal mechanism for change control, especially in development.
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply