Re: Change UDF Owner

PS. Thank you for your patience and explanation. It's amazing after nearly
eight years of administering and programming in SQL Server that there are
still things to learn. These in particular are things you just don't do
every day.


"Arnie Rowland" <arnie@xxxxxxxx> wrote in message
news:OIz0WNrqGHA.2464@xxxxxxxxxxxxxxxxxxxxxxx
It appears that you are trying rename this function in the master
database. Does it exist in the master database?

If not, try connecting to the database where the function exists, and
rename from there.

dbo is like a 'system' account, somewhat similar to 'sa' -except that dbo
is in the context of a database, and can be the schema owner for the the
database. Users can be put in the dbo role, but dbo will still own objects
assigned to it. The generally accepted way to have consistency of
ownership, and thus avoid the 'dreaded ownership chain' problems, is to
have dbo own all objects in a database. In most situations, 'sa', should
own the database, and 'dbo', in most situations should own the objects in
the database.

Think of dbo in terms of not a surrogate 'person', but instead a
'contract' . The objects are under contract, users can be given permission
to interact with the objects belonging to a contract. In SQL 2005, the
concept of 'schema' as 'owner' rather than just schema as DDL is exploited
extensively. It becomes easier to keep users from seeing anything
(including definitions) about objects that belong to schemas (contracts)
that the users have no permissions to use.

--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous


"A McGuire" <allen.mcguire@xxxxxxxxxxxxxxxxx> wrote in message
news:OxBO07qqGHA.2232@xxxxxxxxxxxxxxxxxxxxxxx
dbo could be mapped to another Login Name, however. I don't want the
creator of the database to be the owner, but rather sa for consistency
across all databases and servers. The reason I don't want the creator,
thus dbo, to be the owner is that I've been trying to remove individual
logins and create Local/Domain Windows groups and grant them permissions
to the appropriate Fixed Server Roles or whatever is applicable.

Anyway, that is more subjective. Executing the following:
EXECUTE sp_changeobjectowner
'system_function_schema.fn_replgetagentcommandlinefromjobid', 'dbo'

still results in:
"Object
'master.system_function_schema.fn_replgetagentcommandlinefromjobid' does
not exist or is not a valid object for this operation."

Quite possibly I can't change the owner - no idea. That is the million
dollar question of mine ;-)


"Arnie Rowland" <arnie@xxxxxxxx> wrote in message
news:OsCK%23sqqGHA.4632@xxxxxxxxxxxxxxxxxxxxxxx
I would prefer to have dbo be the owner. It's easier to manage security
if all objects are owned by the same owner.

I think that you need to include the object owner as part of the object
name, e.g.,

EXECUTE sp_changeobjectowner
'system_function_schema.fn_replgetagentcommandlinefromjobid', 'dbo'

--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous


"A McGuire" <allen.mcguire@xxxxxxxxxxxxxxxxx> wrote in message
news:eQvy%23XqqGHA.2304@xxxxxxxxxxxxxxxxxxxxxxx
But for changing the database owner, the following works:
EXEC sp_changedbowner 'sa'

and this doesn't:
EXEC sp_changedbowner 'dbo'

I suppose changing ownership of databases and objects is different. I
have never had to do this, but the UDFs are owned by
'system_function_schema' currently. Does it make sense to change the
ownership to dbo? I'm trying to get all database objects owned by dbo
(which is the same as the 'sa' Login Name).

I still don't have the correct syntax - using what you suggested
returns:
"Object <object_name> does not exist or is not a valid object for this
operation."

I wonder if it has something to do with the fact that it is a UDF?


"Arnie Rowland" <arnie@xxxxxxxx> wrote in message
news:eWTKcPqqGHA.4760@xxxxxxxxxxxxxxxxxxxxxxx
It's because sa doesn't own the database objects, but dbo can.

EXECUTE sp_changeobjectowner 'fn_replgetagentcommandlinefromjobid',
'dbo'

should work for you.

--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."



"A McGuire" <allen.mcguire@xxxxxxxxxxxxxxxxx> wrote in message
news:Ok8BDKqqGHA.4932@xxxxxxxxxxxxxxxxxxxxxxx
Can I change the owner of a UDF?

Example:

USE master
GO
EXEC sp_changeobjectowner 'fn_replgetagentcommandlinefromjobid', 'sa'

... isn't working. I want dbo to own all the objects in my
database - just a security best practice sort of thing. I can't seem
to find my answer searching the web.













.

Relevant Pages

  • Re: How to prevent DELETEs in a table
    ... It is the dbo database USER, not server-level groups, that determins ... It has implicit permissions that can not be denied. ... SQL Server just skips any permission validation for sysadmins. ...
    (microsoft.public.sqlserver.server)
  • Re: conflicting object names in sql server 2000
    ... The owner of the object will depend on the user ... On the local database server when it does the select * from ... she must specify the owner: ...
    (microsoft.public.sqlserver.server)
  • Re: Confused about dbo
    ... it turns out that the owner of the database is a user called ... "dbcreator" which looks like is a predefine server role. ... You said "A third way to have the username dbo is that your login name ...
    (microsoft.public.sqlserver.security)
  • Re: not creating tables as dbo anymore ?
    ... Database Owner (dbo) ... existing user ID in the database specified by database_name. ... with the login of the current connection. ...
    (microsoft.public.sqlserver.security)
  • Re: Security Problem with AD Group in SQL Server Security Logins area
    ... role they were mapped to the dbo user in each database. ... Jasper Smith (SQL Server MVP) ... >> system role of "System Administrators." ...
    (microsoft.public.sqlserver.security)