OK - I've found pieces of the answer to this question in various places, but
never a complete one.

When referencing UDFs from TSQL, I know that table functions do not require
a "dbo." prefix (or any prefix for that matter), and that scalar functions do
require the prefix. 

So what I want to know is, is it possible to use the "user" function (that
returns the current user) as the prefix instead of having to actually hard
code the user name?  i.e.  why can't I just do something like:
             select user.myfunc()
       instead of having to say:
            select kingd.myfunc()

Is there another way to specify the current user?

Thanks in advance.

Are you saying you have different functions for every user, with the same
name?  Yikes.  Why not pass the USER_NAME() into the function and have the
logic there, instead of having to maintain an object per user?  Who is going
to maintain these functions as users are added/removed?


Show quoteHide quote

Sorry - I haven't explained myself very well.

There is only one user and one set of functions.  The problem is, this is a
product that our customer installs, and we allow them to choose what user to
install the product in at runtime.  Therefore, we do not know during
development what the username will be.  So I when developing our stored procs
we won't know how to prefix the function calls.

Does that clarify this at all?

Show quoteHide quote

Yes, that you are still confusing users and object owners.

My recommendation is to create all tables, procedures and functions with the
dbo. prefix, and to always use that prefix in the code.

A

I'm more than willing to admit my ignorance of the difference.  Most of my
experience is on Oracle :)

If we followed your suggestion of creating everything using the "dbo"
prefix, wouldn't the user be required to be the "dbowner"?   The motivation
by mgmt was to allow the customer to install the product with a few
privileges as possible. 

Sorry if I seem to be missing the point, but the differences in the SQL
Server concepts of login vs users never really made a lot of sense to me.

Thanks for your patience.

Show quoteHide quote

NO.  You need to grant users the right to execute stored procedures, etc.
The owner is not the only person who can see or use it.

This is a fairly common practice, and I see very few SQL 2000 installations
with even a single object owned by anyone but the explicit dbo.

Do you have Books Online?  It may not be very exciting reading, but the
differences are laid out there.

A

Wow......nothing more humbling that learning a new database and it's
peculiarities :)  Thanks for your patience.

I'm convinced there's some fundamental piece of information I'm still
missing.  With the following trivial test case:

create table dbo.my_dbo_table (col1 varchar(10))

Neither of the following work because I'm missing SELECT privileges:

select * from my_dbo_table
select * from dbo.my_dbo_table

So I try:

grant select, insert, update,delete on dbo.my_dbo_table to jdbcuser

But that doesn't work, because I get "Grantor does not have GRANT
permission."   So.........I'm allowed to create objects with dbo. but I then
retain no privileges on them, even though I created them?

Is the dbowner the only one allowed to grant privs on these objects?

Show quoteHide quote

From the Books Online:

<Excerpt href="tsqlref.chm::/ts_ga-gz_8odw.htm">
The members of the sysadmin role can grant any permissions in any database.
Object owners can grant permissions for the objects they own. Members of the
db_owner or db_securityadmin roles can grant any permissions on any
statement or object in their database.
</Excerpt>

I assume you are getting the error because none of the above apply.  Since
you are able to create a dbo-owned object but not access it, it appears you
are a member of the db_ddladmin fixed database role.  Members of that role
can create objects in any schema but that role membership doesn't
necessarily allow you to access or grant permissions on the created objects.
You won't run into this problem if you are also a member of the
db_securityadmin role but you might find it easier to run DDL scripts when
logged in as a sysadmin role member or logged in as the database owner.  In
both of these cases, your database security context will be the 'dbo' user
so all objects will be owned by 'dbo' by default.  Alternatively, you can
run DDL as a db_owner role member but you will need to explicitly specify
'dbo' as the owner in order to create dbo-owned objects.

To add to what Aaron said, most SQL Server installations use dbo exclusively
for object ownership.  This is because one can easily segregate dbo-owned
objects both logically and physically in the same SQL Server instance by
creating objects in different databases.  'dbo' will be used as the default
schema (when no like-named object is owned by the current user) so one
doesn't need to owner-qualify objects, except in the special case of UDFs.
although it is still a Best Practice to always owner-qualify objects.

It is probably best to stick with dbo-ownership if the target database is
dedicated to your application.  BTW, the next version of SQL Server provides
a more clear distinction between owner and schema.  I expect the dbo
ownership practice will lessen in SQL 2005.

Show quoteHide quote

Dwayne King,

It is not the "dbo" prefix, neither the user name. it is the owner of the
object.

What do think will happen when a user, that is not the function owner,
executes the statement?

SQL Server will look for user_no_owner.myfunc() and this will yield an error.


AMB


Show quoteHide quote

Torn over a normalizing decision
Mulitple Create Views in Query Batch
Strange Timeout problem.
Book Recommendations
length of text in row in table