January 5, 2005 at 2:32 pm
Is there a way to make a user script create objects with 'dbo' as the owner? Thanks in advance.
January 5, 2005 at 3:00 pm
Yes. Specify the owner with a two-part name. The user execting the script must either be a member of the db_owner or db_ddladmin database role. For instance:
CREATE PROC dbo.usp_MyProc
AS
SELECT 1
The dbo.usp_MyProc tells SQL Server to create a stored procedure named usp_MyProc belonging to dbo.
K. Brian Kelley
@kbriankelley
January 5, 2005 at 3:05 pm
Thanks Brian,
I just reread what I wrote and I goofed up since I wasn't clear enough on what I wanted. The problem I am having is that my users run create table statements and forget to include dbo as the prefix. And so often the objects are created with them as the owner. Is there a way to make the objects to be owned by dbo automatically?
January 5, 2005 at 7:40 pm
Not without making them the actual database owner or aliasing them as dbo. An alternative might be to run a script periodically that finds all objects not owned by dbo, executes sp_helprotect on each object and stores the results to a temporary table, changes the ownership of each object (which drops any set permissions on the object), and then takes the results of the sp_helprotect and reapplies permissions.
K. Brian Kelley
@kbriankelley
January 5, 2005 at 8:07 pm
Brian,
Sorry for the late response, got carried away at work....many thanks for your response. I think that I may just have to rely on my script (which e-mails me the names of objects not owned by dbo), and manually change ownership of those objects.
January 6, 2005 at 9:57 am
in your scripts add the owner
create table dbo.table_name
( etc, etc)
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply