Re: exporting into a SQL Server schema

Yeah, this is an endlessly confusing topic in SQLS 2005 and 2008. What
you can do is create a custom schema, and then create the objects
inside of the schema. That way you aren't limited by the "dbo"
business. The thing that's most confusing is that the dbo schema isn't
the same thing as the dbo user, which is mapped to sysadmin. I'd
recommend creating a schema that is named something other than dbo --
take a look at the way the objects in the AdventureWorks database are
named. I know it may seem a little off-topic in an Access ng, but I
wrote up SQL Server security topics for the ADO.NET documentation that
hopefully are a little more understandable for developers:
http://msdn.microsoft.com/en-us/library/bb669078.aspx.
http://msdn.microsoft.com/en-us/library/bb669061.aspx covers
user-schema separation specifically:

"Users who are assigned the dbo schema do not inherit the permissions
of the dbo user account. No permissions are inherited from a schema by
users; schema permissions are inherited by the database objects
contained in the schema. "

HTH,

Mary

On Mon, 18 May 2009 15:10:41 -0600, "Albert D. Kallal"
<PleaseNOOOsPAMmkallal@xxxxxxx> wrote:

"Mary Chipman [MSFT]" <mchip@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:p803155bcmve3j0ojgj3979nmdnjjsdu3t@xxxxxxxxxx

It's not a bug, that's the way SQL Server security works -- objects
created by sysadmin are owned by dbo. The fully-qualified name of all
objects goes like this: databaseName.schema.object. Access already
knows the database name from the connection string, so all you need to
specify is schema.object.

Thanks for clearing this up, as I had a actually had near the same problem
(and question) in this regards.

It turns out that I'm moving some tables to a web hosted solution with sql
server. To make a long story short, what this means is that I don't get to
create the database. Me the user NEVER gets administration rights or
privileges to the running instance of SQL server. However, I *can* create
and upsize new tables to that database *after* the database been created.

I thus find that all my tables then get the database name appended in front
of all the tables. Now that you pointed this out, this has become clear to
me. I would perfer to NOT have that database name in front of the sql
objects.

The best solution to the problem would be to have someone who is in
the dbo/sysadmin role create the table.

Ok...golly that not going to be possbile for me, I am not part of the "dbo"
in this case nor will I be.

By the way...thanks for answering this. I was about to jump into this thread
and explain the same problem. and, I was hoping someone would come in and
clear this up a bit further.

So, thanks for this heads up and helping us.....it is much appreciated...

I'm still thinking about a way to rename or change the "databaseName.schema"
for this database. The real problem comes up when I make a copy of this
database. I am just not 100% clear on how to deal with this issue of
databaseName.schema.object when I want to copy/paste views or stored procs
between several of these databases. When working on a server that I have
full control over, they all simply used dbo and I never had a problem (or
gave this issue a second thought).

Now I'm running into problems to be able to easy move my SQL and views
between different databases via cut + copy/paste when I not part of dbo.

I suspect the answer is at the end of the day that in an development
environment I simply have to be part of the dbo schema.
.

Relevant Pages

  • Split messages
    ... This is based off the northwind database. ... Create document schema: ... Select the SQL entry, make sure that we are pointing to the SQL ... For the item select a receive pipeline and name it EmpSplitPipe.btp. ...
    (microsoft.public.biztalk.general)
  • Re: Decouple SQL queries from class in OOP design
    ... If the data is owned by the application, embedding SQL might be ... - It couples the application to the database schema. ... - The "validate employee ID" functionality is likely to be used ...
    (comp.object)
  • Re: Access 2007, SQL 2000
    ... Since users are not dbo, and SQL 2000 used the user name as the schema identifier, you have to make sure that every reference to a SQL Server object is fully qualified with the dbo. ... Normally a user should not be able to create any new objects in the db, but if they do, those new objects will be in the userName schema, not the dbo schema, assuming the user is not a db owner. ...
    (microsoft.public.access.adp.sqlserver)
  • Failed to copy table objects - DTS Wizard
    ... one SQL 2000 database to another - both on the same ... I am using Enterprise Manager which is using SQL ... The name 'dbo' was not found in the ... sa is a member of Server Role "System Administrators" ...
    (microsoft.public.sqlserver.security)
  • RE: SQL Adapter
    ... Did you add the SQL schema with the 'Add Generated Items - Add Adapter' wizard. ... Target namespace and Request and Response root element name. ... > have to be inserted in an SQL Database. ...
    (microsoft.public.biztalk.general)