Re: Change UDF Owner

I'll pass that along to the auditors when they come visit. Since it shows
up as a finding, it's my mission to eliminate those findings. In this case,
it seems that they should ignore these and leave them alone.

I appreciate your feedback!


"Arnie Rowland" <arnie@xxxxxxxx> wrote in message
news:OoNL00zqGHA.3248@xxxxxxxxxxxxxxxxxxxxxxx
Thanks for the additional information.

Yes, that list contains system functions. They are best left alone. The
ownership is appropriate.

The INFORMATION_SCHEMA views are virtual tables (VIEWS) of system
information. The objects starting with 'fn_Repl' are used in Replication,
'fn_trace' used in profiling, etc.

You may wish to alter your 'search' script to ignore objects owned by
system_function_schema -especially in the master database.

And yes, things are constantly changing so there is always something new
for us to learn and master.

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


"A McGuire" <allen.mcguire@xxxxxxxxxxxxxxxxx> wrote in message
news:eczZHTzqGHA.4960@xxxxxxxxxxxxxxxxxxxxxxx
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: 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: Change UDF Owner
    ... Does it exist in the master database? ... Users can be put in the dbo role, but dbo will still own objects ... concept of 'schema' as 'owner' rather than just schema as DDL is exploited ... to the appropriate Fixed Server Roles or whatever is applicable. ...
    (microsoft.public.sqlserver.security)
  • Re: conflicting object names in sql server 2000
    ... On the local database server when it does the select * from ... > michael.xxx Note that michael is the owner of the database. ... it will see if there is one owned by dbo. ...
    (microsoft.public.sqlserver.server)
  • Re: Confused about dbo
    ... Jasper Smith (SQL Server MVP) ... > database within a SQL Server instance. ... > There are several ways you could have the username dbo. ... > the true owner of the database. ...
    (microsoft.public.sqlserver.security)
  • Re: Change dbo
    ... I have a database where a user is defined as the dbo on a database. ... I tried using SQL Server Manger ... The login for this person maps to dbo. ...
    (microsoft.public.sqlserver.security)