exporting into a SQL Server schema

[myotheraccount]

I'm not sure if this belongs here or in MSSQL Server - probably both.
I'm trying to export a table from Access into SQL Server. The issue
is that I want to export it into the dbo schema. Since I log into SQL
Server using windows authentication through my AD group and I my group
does not have sysadmin privelages, the schema is defaulted to my
username (It is unclear if this is a bug in SQL Server, but it sure
seems that way to me.)
Is there any way I can specify the schema name when exporting? If I
enter dbo.Table1 as my table name to export to, I get an error
message: "The object name 'dbo.Table1' you entereed doesn't follow
Microsoft Office Access object-name rules."
Any ideas?
Thanks

 

[Mary Chipman [MSFT]]

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.

The best solution to the problem would be to have someone who is in
the dbo/sysadmin role create the table. Then all you need is
permission to insert data. If this is something that you do
repeatedly, then create an extra column in the SQL Server table that
indicates the datetime the data was inserted.

--Mary

 

[Mary Chipman [MSFT]]

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.

The best solution to the problem would be to have someone who is in
the dbo/sysadmin role create the table. Then all you need is
permission to insert data. If this is something that you do
repeatedly, then create an extra column in the SQL Server table that
indicates the datetime the data was inserted.

--Mary

 

[Albert D. Kallal]

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.

 

[Albert D. Kallal]

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.

 

[Mary Chipman [MSFT]]

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

 

[Mary Chipman [MSFT]]

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