Owership of database objects, Yu'll login to use?

I am very new to SQL Server 2000 but was a Sybase DBA for 2 years. In
Sybase we would alias a developer to DBO (of that database only) so
that all objects (tables/stored procedures) would show up as owned by
DBO.
In SQL Server it looks to me that the only way to get obects owned by
DBO is to load them as SA or add the developer to the fixed role of
sysadmin. Neither of those appears to be the way to go.
So I'm wondering how other shows handle this issue. Do you set up an
specific account for loading objects and maintaining a SQL server
database?
Thanks,
Randy K
wawork@.hotmail.comHi Randy
I worked with Sybase for 8 years, and have been working with Microsoft SQL
Server since its inception.
In SQL Server, you can alias logins to the dbo user of a database exactly
the same way you do with Sybase, with the sp_addalias procedure.
Is it possible you're trying to achieve this functionality using the
db_owner role? Putting a user in that role will give her all the rights and
privileges of the database owner, but her name will still be her own name,
and not dbo. So any objects she creates, by default will not be owned by
dbo. However, someone in the db_owner role can create objects and specify
that the owner should be dbo:
CREATE TABLE dbo.newtable
(column ...)
So there are really two different things going on. You can actually give
someone the name dbo using sp_addalias, or you can put them in the db_owner
role, and they can specify that new obects are to be owned by dbo.
It's actually recommended that you always specify the owner name of objects,
both when creating them and when referencing them, and if you get into this
habit, you don't lose anything by just using the db_owner role.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Randy K" <wawork@.hotmail.com> wrote in message
news:3f27f6bc.59865578@.msnews.microsoft.com...
> I am very new to SQL Server 2000 but was a Sybase DBA for 2 years. In
> Sybase we would alias a developer to DBO (of that database only) so
> that all objects (tables/stored procedures) would show up as owned by
> DBO.
> In SQL Server it looks to me that the only way to get obects owned by
> DBO is to load them as SA or add the developer to the fixed role of
> sysadmin. Neither of those appears to be the way to go.
> So I'm wondering how other shows handle this issue. Do you set up an
> specific account for loading objects and maintaining a SQL server
> database?
> Thanks,
> Randy K
> wawork@.hotmail.com|||Unfortunately, a user with the db_owner role can
> not change the ownership of a object when creating it using the ESRI
> software.
Yes, this can be a problem. Even Microsoft's own Enterprise Manager didn't
allow specifying a different owner in SQL Server 7, and although SQL 2000
allows it, it is not obvious how to do it. That's one of the reasons why I
never use GUIs to create tables. :-)
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Randy K" <wawork@.hotmail.com> wrote in message
news:3f284734.18529640@.msnews.microsoft.com...
> Thank you Kalen that's what I was looking for. I had always used
> Sybase Central GUI to alias users as DBO and mistaking thought
> assigning a user to the db_owner role in SQL Server was the same
> thing.
> I'll need to do some testing since we are using SQL Server with ArcSDE
> by ESRI on top. Of the two methods only the first, spp_addalias,
> works with ArcSDE. Unfortunately, a user with the db_owner role can
> not change the ownership of a object when creating it using the ESRI
> software.
>
> On Wed, 30 Jul 2003 10:21:16 -0700, "Kalen Delaney"
> <replies@.public_newsgroups.com> wrote:
> >Hi Randy
> >
> >I worked with Sybase for 8 years, and have been working with Microsoft
SQL
> >Server since its inception.
> >
> >In SQL Server, you can alias logins to the dbo user of a database exactly
> >the same way you do with Sybase, with the sp_addalias procedure.
> >
> >Is it possible you're trying to achieve this functionality using the
> >db_owner role? Putting a user in that role will give her all the rights
and
> >privileges of the database owner, but her name will still be her own
name,
> >and not dbo. So any objects she creates, by default will not be owned by
> >dbo. However, someone in the db_owner role can create objects and specify
> >that the owner should be dbo:
> >
> >CREATE TABLE dbo.newtable
> >(column ...)
> >
> >So there are really two different things going on. You can actually give
> >someone the name dbo using sp_addalias, or you can put them in the
db_owner
> >role, and they can specify that new obects are to be owned by dbo.
> >
> >It's actually recommended that you always specify the owner name of
objects,
> >both when creating them and when referencing them, and if you get into
this
> >habit, you don't lose anything by just using the db_owner role.
> >
> >--
> >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"Randy K" <wawork@.hotmail.com> wrote in message
> >news:3f27f6bc.59865578@.msnews.microsoft.com...
> >> I am very new to SQL Server 2000 but was a Sybase DBA for 2 years. In
> >> Sybase we would alias a developer to DBO (of that database only) so
> >> that all objects (tables/stored procedures) would show up as owned by
> >> DBO.
> >>
> >> In SQL Server it looks to me that the only way to get obects owned by
> >> DBO is to load them as SA or add the developer to the fixed role of
> >> sysadmin. Neither of those appears to be the way to go.
> >>
> >> So I'm wondering how other shows handle this issue. Do you set up an
> >> specific account for loading objects and maintaining a SQL server
> >> database?
> >>
> >> Thanks,
> >> Randy K
> >> wawork@.hotmail.com
> >
> >
>

Overflow error

Hi:

I am trying to pump data from Sybase to SQL Server using SSIS and I get this error:

Conversion failed because the data overflowed the specified type

The data on the external column metadata shows as type database timestamp, as does the output column. The database values are all datetime, coming in through OLEDB to Sybase. Any idea what could be going on here?

Thanks,

Kayda

I should correct, the data is coming from a datetime field in Sybase to a datetime field in SQL Server. The values in Sybase are all datetime--it runs for awhile and then gives this error.

Any ideas?
|||Just wondering whether anyone has the answer to this--this problem is cropping up again. There isn't nothing wrong in Sybase with the datetime field as far as I can tell...|||

A quick search did not yield any good results on differences between SQL Server and Sybase DATETIME data types. The thing that seems most likely to me that there are different data ranges supported by each platform.

Can you select a MIN and MAX of the problem field when the error is occuring?

|||

Yes, I looked for NULLs and did a MIN and MAX on Sybase--just normal dates from 2004 up until "yesterday". I am running this pump on many other similar tables with the same time field. Is there a way to figure out exactly is going on?

Thanks,

Kayda

|||

Hi Kayda,

Is the error coming from the source component, or the destination (on insertion)?

What are your source and destination components?

Can you try to hook up an error output, and have the error rows redirected to see if you can identify the data that is failing?

Thanks

Mark

|||

I tried something like that, but didn't seem to work. Do you mean just hooking a table to the red arrow or putting a transform in between? Could you give me an idea of what might work in this particular case?

|||

Kayda_SQL wrote:

I tried something like that, but didn't seem to work. Do you mean just hooking a table to the red arrow or putting a transform in between? Could you give me an idea of what might work in this particular case?

It might be faster to connect the red arrow to a row count transformation and then add a data viewer to that path. But yes, you could hook that red arrow up to an OLE DB destination and store the contents in a table.

Just make sure that you aren't using the fast load option (for this test) so that you get each row in error instead of a full batch. Know that this is much slower though -- but it should give you the data at least along with an error code.

Overflow error

Hi:

I am trying to pump data from Sybase to SQL Server using SSIS and I get this error:

Conversion failed because the data overflowed the specified type

The data on the external column metadata shows as type database timestamp, as does the output column. The database values are all datetime, coming in through OLEDB to Sybase. Any idea what could be going on here?

Thanks,

Kayda

I should correct, the data is coming from a datetime field in Sybase to a datetime field in SQL Server. The values in Sybase are all datetime--it runs for awhile and then gives this error.

Any ideas?|||Just wondering whether anyone has the answer to this--this problem is cropping up again. There isn't nothing wrong in Sybase with the datetime field as far as I can tell...|||

A quick search did not yield any good results on differences between SQL Server and Sybase DATETIME data types. The thing that seems most likely to me that there are different data ranges supported by each platform.

Can you select a MIN and MAX of the problem field when the error is occuring?

|||

Yes, I looked for NULLs and did a MIN and MAX on Sybase--just normal dates from 2004 up until "yesterday". I am running this pump on many other similar tables with the same time field. Is there a way to figure out exactly is going on?

Thanks,

Kayda

|||

Hi Kayda,

Is the error coming from the source component, or the destination (on insertion)?

What are your source and destination components?

Can you try to hook up an error output, and have the error rows redirected to see if you can identify the data that is failing?

Thanks

Mark

|||

I tried something like that, but didn't seem to work. Do you mean just hooking a table to the red arrow or putting a transform in between? Could you give me an idea of what might work in this particular case?

|||

Kayda_SQL wrote:

I tried something like that, but didn't seem to work. Do you mean just hooking a table to the red arrow or putting a transform in between? Could you give me an idea of what might work in this particular case?

It might be faster to connect the red arrow to a row count transformation and then add a data viewer to that path. But yes, you could hook that red arrow up to an OLE DB destination and store the contents in a table.

Just make sure that you aren't using the fast load option (for this test) so that you get each row in error instead of a full batch. Know that this is much slower though -- but it should give you the data at least along with an error code.

Overflow error

Hi:

I am trying to pump data from Sybase to SQL Server using SSIS and I get this error:

Conversion failed because the data overflowed the specified type

The data on the external column metadata shows as type database timestamp, as does the output column. The database values are all datetime, coming in through OLEDB to Sybase. Any idea what could be going on here?

Thanks,

Kayda

I should correct, the data is coming from a datetime field in Sybase to a datetime field in SQL Server. The values in Sybase are all datetime--it runs for awhile and then gives this error.

Any ideas?|||Just wondering whether anyone has the answer to this--this problem is cropping up again. There isn't nothing wrong in Sybase with the datetime field as far as I can tell...|||

A quick search did not yield any good results on differences between SQL Server and Sybase DATETIME data types. The thing that seems most likely to me that there are different data ranges supported by each platform.

Can you select a MIN and MAX of the problem field when the error is occuring?

|||

Yes, I looked for NULLs and did a MIN and MAX on Sybase--just normal dates from 2004 up until "yesterday". I am running this pump on many other similar tables with the same time field. Is there a way to figure out exactly is going on?

Thanks,

Kayda

|||

Hi Kayda,

Is the error coming from the source component, or the destination (on insertion)?

What are your source and destination components?

Can you try to hook up an error output, and have the error rows redirected to see if you can identify the data that is failing?

Thanks

Mark

|||

I tried something like that, but didn't seem to work. Do you mean just hooking a table to the red arrow or putting a transform in between? Could you give me an idea of what might work in this particular case?

|||

Kayda_SQL wrote:

I tried something like that, but didn't seem to work. Do you mean just hooking a table to the red arrow or putting a transform in between? Could you give me an idea of what might work in this particular case?

It might be faster to connect the red arrow to a row count transformation and then add a data viewer to that path. But yes, you could hook that red arrow up to an OLE DB destination and store the contents in a table.

Just make sure that you aren't using the fast load option (for this test) so that you get each row in error instead of a full batch. Know that this is much slower though -- but it should give you the data at least along with an error code.