Re: Change UDF Owner

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.name,
[' + @dbnm + '].dbo.sysobjects.name from [' + @dbnm + '].dbo.sysobjects, ['
+ @dbnm + '].dbo.sysusers where [' + @dbnm + '].dbo.sysobjects.uid = [' +
@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@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: Replication problems
    ... An error occurred while accessing the DHCP database. ... DHCP server event log for more information on this error. ... browser for the domain on transport NetBT_Tcpip_{99B638B5-2C0B. ... master browser is stopping or an election is being forced. ...
    (microsoft.public.windows.server.active_directory)
  • RE: SQL Server failing to start (directly effecting sales!!!) Error: 823, Severity:
    ... You should consider calling Microsoft Technical Support as your master ... They will want your SQL Server Errorlogs ... that the machine with the database has hardware that is failing. ... Even if you are planning to reinstall you should make a backup copy ...
    (microsoft.public.sqlserver.msde)
  • Re: Table Normalization
    ... To master it requires a great deal more work. ... There are so many categories to work in (database design, queries, forms, ... that they'd flub a number of the answers, ... make the same mistake twice, and they don't let others make the same ...
    (microsoft.public.access.tablesdbdesign)
  • Copy FE of DB when it is already open on the server
    ... I just recently changed my database that I'm running from a monolithic ... They click a shortcut, which used to lead to the old ... network (the master copy that Tony's program copies out to each user) ... by itself (as apposed to putting it in the same folder with the ...
    (comp.databases.ms-access)
  • RE: Multi user database
    ... manner and push updates to the backend. ... the other user updates table c then run the append query. ... Right now I can determine when the master table is updated, ... the database they should 'share' the data not update to/from local copies. ...
    (microsoft.public.access.modulesdaovba)