Change UDF Owner

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.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@.gmail.com.invalid> wrote in message
news:Ok8BDKqqGHA.4932@.TK2MSFTNGP05.phx.gbl...
> 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.
>|||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@.1568.com> wrote in message
news:eWTKcPqqGHA.4760@.TK2MSFTNGP05.phx.gbl...
> 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@.gmail.com.invalid> wrote in message
> news:Ok8BDKqqGHA.4932@.TK2MSFTNGP05.phx.gbl...
>|||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@.gmail.com.invalid> wrote in message
news:eQvy%23XqqGHA.2304@.TK2MSFTNGP03.phx.gbl...
> 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@.1568.com> wrote in message
> news:eWTKcPqqGHA.4760@.TK2MSFTNGP05.phx.gbl...
>|||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@.1568.com> wrote in message
news:OsCK%23sqqGHA.4632@.TK2MSFTNGP05.phx.gbl...
>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@.gmail.com.invalid> wrote in message
> news:eQvy%23XqqGHA.2304@.TK2MSFTNGP03.phx.gbl...
>|||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@.gmail.com.invalid> wrote in message
news:OxBO07qqGHA.2232@.TK2MSFTNGP04.phx.gbl...
> 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@.1568.com> wrote in message
> news:OsCK%23sqqGHA.4632@.TK2MSFTNGP05.phx.gbl...
>|||These are all default UDF's that seem to come with a standard installation,
and are all in the master database. Maybe I'll just forget about those
objects for now since they are not owned by people, but rather
system_function_schema and dbo (mix of each).
You may wonder my reason for trying to do this. I have a script that the
Government provides us, and one of the items that comes up as a 'finding' is
objects not owned by the database owner.
The script (minus some declarations probably):
-- DM1759: Unauthorized Object Owners V0002462
set @.iter = 0
DECLARE dbcur CURSOR FOR SELECT name FROM sysdatabases
OPEN dbcur
FETCH NEXT FROM dbcur
INTO @.dbnm
WHILE @.@.FETCH_STATUS = 0
BEGIN
exec ('declare db1cur cursor for select [' + @.dbnm + '].dbo.sysusers.nam
e,
[' + @.dbnm + '].dbo.sysobjects.name from [' + @.dbnm + '].dbo.sysobj
ects, ['
+ @.dbnm + '].dbo.sysusers where [' + @.dbnm + '].dbo.sysobjects.uid =
1;' +
@.dbnm + '].dbo.sysusers.uid and [' + @.dbnm + '].dbo.sysobjects.uid <> 1
and
[' + @.dbnm + '].dbo.sysusers.name <> "Information_Schema" and [' + @.
dbnm +
'].dbo.sysusers.name <> "System_function_schema"')
open db1cur
FETCH NEXT FROM db1cur
INTO @.uname,@.objname
WHILE @.@.FETCH_STATUS = 0
begin
set @.iter = @.iter + 1
INSERT INTO #tmpsrrfindings VALUES ( 'V0002462', @.iter, @.dbnm, @.uname,
@.objname,'','')
FETCH NEXT FROM db1cur
INTO @.uname,@.objname
end
CLOSE db1cur
DEALLOCATE db1cur
-- Get the next database
FETCH NEXT FROM dbcur
INTO @.dbnm
END
CLOSE dbcur
DEALLOCATE dbcur
If @.iter > 0
begin
Update #tmpsrrpdi set finding = 'O' where pdi = 'V0002462'
end
Else /* No owners */
Begin
Update #tmpsrrpdi set finding = 'NF' where pdi = 'V0002462'
end
The results:
The following objects are not owned by the database owner:
master system_function_schema fn_replgetagentcommandlinefromjobid
master system_function_schema fn_listextendedproperty
master system_function_schema fn_get_sql
master system_function_schema fn_serverid
master system_function_schema fn_getpersistedservernamecasevariation
master system_function_schema fn_escapecmdshellsymbols
master system_function_schema fn_escapecmdshellsymbolsremovequotes
master system_function_schema fn_repltrimleadingzerosinhexstr
master system_function_schema fn_replinttobitstring
master system_function_schema fn_replbitstringtoint
master system_function_schema fn_replrotr
master system_function_schema fn_replgenerateshorterfilenameprefix
master system_function_schema fn_replcomposepublicationsnapshotfolder
master system_function_schema fn_replgetbinary8lodword
master system_function_schema fn_replprepadbinary8
master system_function_schema fn_replmakestringliteral
master system_function_schema fn_repladjustcolumnmap
master system_function_schema fn_replquotename
master INFORMATION_SCHEMA SCHEMATA
master system_function_schema fn_chariswhitespace
master INFORMATION_SCHEMA TABLES
master system_function_schema fn_generateparameterpattern
master INFORMATION_SCHEMA TABLE_CONSTRAINTS
master system_function_schema fn_skipparameterargument
master INFORMATION_SCHEMA TABLE_PRIVILEGES
master system_function_schema fn_removeparameterwithargument
master INFORMATION_SCHEMA COLUMNS
master system_function_schema fn_dblog
master system_function_schema fn_updateparameterwithargument
master INFORMATION_SCHEMA COLUMN_DOMAIN_USAGE
master system_function_schema fn_helpcollations
master INFORMATION_SCHEMA COLUMN_PRIVILEGES
master system_function_schema fn_trace_getinfo
master system_function_schema fn_repluniquename
master INFORMATION_SCHEMA DOMAINS
master system_function_schema fn_trace_geteventinfo
master INFORMATION_SCHEMA DOMAIN_CONSTRAINTS
master system_function_schema fn_trace_getfilterinfo
master INFORMATION_SCHEMA KEY_COLUMN_USAGE
master system_function_schema fn_trace_gettable
master INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS
master system_function_schema fn_servershareddrives
master INFORMATION_SCHEMA CHECK_CONSTRAINTS
master system_function_schema fn_virtualfilestats
master INFORMATION_SCHEMA CONSTRAINT_TABLE_USAGE
master system_function_schema fn_virtualservernodes
master INFORMATION_SCHEMA CONSTRAINT_COLUMN_USAGE
master INFORMATION_SCHEMA VIEWS
master INFORMATION_SCHEMA VIEW_TABLE_USAGE
master INFORMATION_SCHEMA VIEW_COLUMN_USAGE
master INFORMATION_SCHEMA ROUTINES
master INFORMATION_SCHEMA PARAMETERS
master INFORMATION_SCHEMA ROUTINE_COLUMNS
Does that help with what I'm trying to do here?
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OIz0WNrqGHA.2464@.TK2MSFTNGP03.phx.gbl...
> 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@.gmail.com.invalid> wrote in message
> news:OxBO07qqGHA.2232@.TK2MSFTNGP04.phx.gbl...
>|||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@.1568.com> wrote in message
news:OIz0WNrqGHA.2464@.TK2MSFTNGP03.phx.gbl...
> 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@.gmail.com.invalid> wrote in message
> news:OxBO07qqGHA.2232@.TK2MSFTNGP04.phx.gbl...
>|||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@.gmail.com.invalid> wrote in message
news:eczZHTzqGHA.4960@.TK2MSFTNGP04.phx.gbl...
> 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@.1568.com> wrote in message
> news:OIz0WNrqGHA.2464@.TK2MSFTNGP03.phx.gbl...
>|||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@.1568.com> wrote in message
news:OoNL00zqGHA.3248@.TK2MSFTNGP04.phx.gbl...
> 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@.gmail.com.invalid> wrote in message
> news:eczZHTzqGHA.4960@.TK2MSFTNGP04.phx.gbl...
>