Ownership issue

One of my apps uses a login that is tied down to only use the sprocs and tables it is allowed to access. Its just given 'public' role. However, it needs to be able to add and delete fields from one table in particular. Is there a way to allow this? The login it uses has been given full rights to the table but an alter table command faults. The tbale must be a 'dbo' because other apps use it -- can I be granted DDL rights on a per table basis?Enterprise manager allow for table access specifically to tables, vies, store procedures etc... Right can be assigned per table or view. Read, Write, Update, Delete.|||Yes they can...but not for alter table it seems.|||Alter table? Dump it out and bring it back in ussing DTS utl for easy cleanup.|||Huh?

I want my app to issue an alter table command in SQL Server, it seems impossible without having dbowner in login -- which would seem massive overkill|||According to the BOL in the description of the GRANT (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_con01_85f7.asp) command, in thefootnote 1 for ALTER TABLE it says that the object owner can issue ALTER TABLE commands for the objects that they own. The table shows that the db_owner and db_ddladmin roles can issue ALTER TABLE commands for objects owned by other users.

-PatP|||Thanks for clearing that up. Looks like I am stuffed really. Ah well...

I have got around it temporarily by raising the rights on the accout, doing the operation and then lowering them.

Owner of the database after restore

I restored a database SynComp_TEST from a backup using a login testbinu,
which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
I got below message ..
Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
file1.
Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on file
1.
Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file 1.
Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
Msg 916, Level 14, State 1, Server BOYD, Line 1
Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
Msg 3013, Level 16, State 1, Server BOYD, Line 1
RESTORE DATABASE is terminating abnormally.
Now I think the owner of the database is 'sa ' ?Am I right ?
Is there any way I can restore this database with testbinu as the owner ?
Or at least testbinu as a valid user in the database?
Hi,
First you have to correct the user testbinu. The error has come because the
login testbinu was not there in the master database syslogins table.
You can rectify the problem using the procedure sp_change_users_login (see
sql server books online on usage)
After builing up the Login/ user chain , you can change the database owner
to testbinu if you need
use <dbname>
go
sp_changedbowner testbinu
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:#34ScB$cEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I restored a database SynComp_TEST from a backup using a login testbinu,
> which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
> I got below message ..
> Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
> file1.
> Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on
file
> 1.
> Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file
1.
> Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
> Msg 916, Level 14, State 1, Server BOYD, Line 1
> Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
> Msg 3013, Level 16, State 1, Server BOYD, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> Now I think the owner of the database is 'sa ' ?Am I right ?
> Is there any way I can restore this database with testbinu as the owner ?
> Or at least testbinu as a valid user in the database?
>

Owner of the database after restore

I restored a database SynComp_TEST from a backup using a login testbinu,
which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
I got below message ..
Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
file1.
Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on file
1.
Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file 1.
Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
Msg 916, Level 14, State 1, Server BOYD, Line 1
Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
Msg 3013, Level 16, State 1, Server BOYD, Line 1
RESTORE DATABASE is terminating abnormally.
Now I think the owner of the database is 'sa ' ?Am I right ?
Is there any way I can restore this database with testbinu as the owner ?
Or at least testbinu as a valid user in the database?Hi,
First you have to correct the user testbinu. The error has come because the
login testbinu was not there in the master database syslogins table.
You can rectify the problem using the procedure sp_change_users_login (see
sql server books online on usage)
After builing up the Login/ user chain , you can change the database owner
to testbinu if you need
use <dbname>
go
sp_changedbowner testbinu
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:#34ScB$cEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I restored a database SynComp_TEST from a backup using a login testbinu,
> which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
> I got below message ..
> Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
> file1.
> Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on
file
> 1.
> Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file
1.
> Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
> Msg 916, Level 14, State 1, Server BOYD, Line 1
> Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
> Msg 3013, Level 16, State 1, Server BOYD, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> Now I think the owner of the database is 'sa ' ?Am I right ?
> Is there any way I can restore this database with testbinu as the owner ?
> Or at least testbinu as a valid user in the database?
>|||Follow the steps that Hari outlined for you to change the
owner.
To check a db owner, you can execute:
sp_helpdb
-Sue
On Tue, 27 Jul 2004 11:40:11 -0400, "Abraham"
<binu_ca@.yahoo.com> wrote:

>I restored a database SynComp_TEST from a backup using a login testbinu,
>which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
>I got below message ..
>Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
>file1.
>Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on fil
e
>1.
>Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file 1
.
>Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
>Msg 916, Level 14, State 1, Server BOYD, Line 1
>Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
>Msg 3013, Level 16, State 1, Server BOYD, Line 1
>RESTORE DATABASE is terminating abnormally.
>
>Now I think the owner of the database is 'sa ' ?Am I right ?
>Is there any way I can restore this database with testbinu as the owner ?
>Or at least testbinu as a valid user in the database?
>

Owner of the database after restore

I restored a database SynComp_TEST from a backup using a login testbinu,
which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
I got below message ..
Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
file1.
Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on file
1.
Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file 1.
Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
Msg 916, Level 14, State 1, Server BOYD, Line 1
Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
Msg 3013, Level 16, State 1, Server BOYD, Line 1
RESTORE DATABASE is terminating abnormally.
Now I think the owner of the database is 'sa ' ?Am I right ?
Is there any way I can restore this database with testbinu as the owner ?
Or at least testbinu as a valid user in the database?Hi,
First you have to correct the user testbinu. The error has come because the
login testbinu was not there in the master database syslogins table.
You can rectify the problem using the procedure sp_change_users_login (see
sql server books online on usage)
After builing up the Login/ user chain , you can change the database owner
to testbinu if you need
use <dbname>
go
sp_changedbowner testbinu
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:#34ScB$cEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I restored a database SynComp_TEST from a backup using a login testbinu,
> which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
> I got below message ..
> Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
> file1.
> Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on
file
> 1.
> Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file
1.
> Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
> Msg 916, Level 14, State 1, Server BOYD, Line 1
> Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
> Msg 3013, Level 16, State 1, Server BOYD, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> Now I think the owner of the database is 'sa ' ?Am I right ?
> Is there any way I can restore this database with testbinu as the owner ?
> Or at least testbinu as a valid user in the database?
>

Owner of the database after restore

I restored a database SynComp_TEST from a backup using a login testbinu,
which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
I got below message ..
Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
file1.
Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on file
1.
Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file 1.
Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
Msg 916, Level 14, State 1, Server BOYD, Line 1
Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
Msg 3013, Level 16, State 1, Server BOYD, Line 1
RESTORE DATABASE is terminating abnormally.
Now I think the owner of the database is 'sa ' ?Am I right ?
Is there any way I can restore this database with testbinu as the owner ?
Or at least testbinu as a valid user in the database?Hi,
First you have to correct the user testbinu. The error has come because the
login testbinu was not there in the master database syslogins table.
You can rectify the problem using the procedure sp_change_users_login (see
sql server books online on usage)
After builing up the Login/ user chain , you can change the database owner
to testbinu if you need
use <dbname>
go
sp_changedbowner testbinu
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:#34ScB$cEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I restored a database SynComp_TEST from a backup using a login testbinu,
> which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
> I got below message ..
> Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
> file1.
> Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on
file
> 1.
> Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file
1.
> Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
> Msg 916, Level 14, State 1, Server BOYD, Line 1
> Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
> Msg 3013, Level 16, State 1, Server BOYD, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> Now I think the owner of the database is 'sa ' ?Am I right ?
> Is there any way I can restore this database with testbinu as the owner ?
> Or at least testbinu as a valid user in the database?
>

Owner of DB missing login name

SQL 2K on W2K Server...

ok. created 'user1'. made him a sys admin. created DB#1 with his credentials--he is the owner. everyone uses his credentials to create their odbc's--no problem. within EM, under db user properties for DB#1, it shows he is the owner and that his user name is 'user1' and his login name is 'dbo'.

for some reason today, the login name for this user within DB#1's db properties says <None>, as opposed to dbo. and though this same panel (Database User poperties - User1) shows that he is the owner of the db, checking 'Security-->Logins-->SQL Server Login Properties-->Database Access tab doesn't even have a checkbox under the 'Permit' column. and, when i check the Permit box, and check 'db_owner' in the 'Permit in Database role' window, i receive an error that Error 21002: [SQL-DMO]User 'User1' already exists.

well, i should hope so! but why has his ownership been taken away? why can't i re-add it? all pointers on the net for this error talk about issues with moving or restoring db's--neither of which was done in this case.

help ?need to bump this one up--sry

Originally posted by tojolo
SQL 2K on W2K Server...

ok. created 'user1'. made him a sys admin. created DB#1 with his credentials--he is the owner. everyone uses his credentials to create their odbc's--no problem. within EM, under db user properties for DB#1, it shows he is the owner and that his user name is 'user1' and his login name is 'dbo'.

for some reason today, the login name for this user within DB#1's db properties says <None>, as opposed to dbo. and though this same panel (Database User poperties - User1) shows that he is the owner of the db, checking 'Security-->Logins-->SQL Server Login Properties-->Database Access tab doesn't even have a checkbox under the 'Permit' column. and, when i check the Permit box, and check 'db_owner' in the 'Permit in Database role' window, i receive an error that Error 21002: [SQL-DMO]User 'User1' already exists.

well, i should hope so! but why has his ownership been taken away? why can't i re-add it? all pointers on the net for this error talk about issues with moving or restoring db's--neither of which was done in this case.

help ?

Owner granting permissions on his objetcs

hi all,
I've run the follwing code:
use adventureworks
go
create login a with password = '123'
create login b with password = '123'
create user a
create user b
go
grant create table on database::adventureworks to a
grant alter schema::dbo to a
grant select on schema::dbo to a
execute as login = 'a'
create table dbo.aTab (col1 int)
grant select on object::dbo.aTab to b
the grant statment failed.
how come an object owner can't grant select permission on his object to
other db users?
what does it take, at the minimum, to allow him to do that?
thanks
tomTom
add
grant control on schema::dbo to a
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:1C3EB310-7915-467C-852C-EF6CFC65CB57@.microsoft.com...
> hi all,
> I've run the follwing code:
> use adventureworks
> go
> create login a with password = '123'
> create login b with password = '123'
> create user a
> create user b
> go
> grant create table on database::adventureworks to a
> grant alter schema::dbo to a
> grant select on schema::dbo to a
> execute as login = 'a'
> create table dbo.aTab (col1 int)
> grant select on object::dbo.aTab to b
> the grant statment failed.
> how come an object owner can't grant select permission on his object to
> other db users?
> what does it take, at the minimum, to allow him to do that?
> thanks
> tom
>
>|||hi Uri,
grant control makes the user have full control on the chema.
I only want him to be able to grant other users select permission on his
table.
thanks,
tom.
"Uri Dimant" wrote:

> Tom
> add
> grant control on schema::dbo to a
>
>
>
> "Tom" <Tom@.discussions.microsoft.com> wrote in message
> news:1C3EB310-7915-467C-852C-EF6CFC65CB57@.microsoft.com...
>
>

owned schema or role members?

Hi,
I want to create a login (for account ASPNET from the Active Directory) in
sql server express 2005 for a specific database.
When addidng a new user to a specific database, i see:
Owned schemas, where i take db_datareader and db_datawriter
Roles members: also db_datareader and db_datawriter
What's the difference between both and are they both required fpr account
ASPNET?
Tbanks
BartBart
--db_datareader
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/7d71fca8-ad8d-49c5-b4cc-c1cd
ab0fab43.htm
--db_datawriter
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/08a5c847-f993-4402-b3ac-a511
3f41e8c8.htm
"Bart" <b@.sdq.dc> wrote in message
news:OMfgIZlbHHA.3420@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I want to create a login (for account ASPNET from the Active Directory) in
> sql server express 2005 for a specific database.
> When addidng a new user to a specific database, i see:
> Owned schemas, where i take db_datareader and db_datawriter
> Roles members: also db_datareader and db_datawriter
> What's the difference between both and are they both required fpr account
> ASPNET?
> Tbanks
> Bart
>
>
>|||Thanks, but my question was more about the difference between Owned schema
and Role members.
Is it enough to take db_anything in Role members or must the user also owns
a schema with db_anything?
"Uri Dimant" <urid@.iscar.co.il> schreef in bericht
news:eDumYbsbHHA.2088@.TK2MSFTNGP04.phx.gbl...
> Bart
> --db_datareader
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/7d71fca8-ad8d-49c5-b4cc-c1
cdab0fab43.htm
>
> --db_datawriter
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/08a5c847-f993-4402-b3ac-a5
113f41e8c8.htm
>
> "Bart" <b@.sdq.dc> wrote in message
> news:OMfgIZlbHHA.3420@.TK2MSFTNGP05.phx.gbl...
>|||Bart
SCHEMA and Roles are different things. When you create a user ut should be
mapped to schema you have specified ir DEFAULT schema--DBO.
Can you elaborate on what you are trying to achive?
"Bart" <b@.sdq.dc> wrote in message
news:uEq8NwvbHHA.4140@.TK2MSFTNGP06.phx.gbl...
> Thanks, but my question was more about the difference between Owned schema
> and Role members.
> Is it enough to take db_anything in Role members or must the user also
> owns a schema with db_anything?
> "Uri Dimant" <urid@.iscar.co.il> schreef in bericht
> news:eDumYbsbHHA.2088@.TK2MSFTNGP04.phx.gbl...
>|||Well, i want to create a new login for account ASPNET (which runs under
ASP.NET) and then define an user (aspnet) for 'mydatabase'. That user must
get read/write prights to the db. At that level, i see in the window
configuration two things about read /write: Owned Schema and Role members.
So my question is: should i take db_readreader / db_writer in the Ownd
Schema or in Role members or in both?
Thanks
"Uri Dimant" <urid@.iscar.co.il> schreef in bericht
news:%23p8We%232bHHA.4632@.TK2MSFTNGP03.phx.gbl...
> Bart
> SCHEMA and Roles are different things. When you create a user ut should be
> mapped to schema you have specified ir DEFAULT schema--DBO.
> Can you elaborate on what you are trying to achive?
>
> "Bart" <b@.sdq.dc> wrote in message
> news:uEq8NwvbHHA.4140@.TK2MSFTNGP06.phx.gbl...
>|||Bart
Does the user should run queries which manipulate with tables belong to
anohter SCHEMA?
I'd go with ROLES and grant SELECT/EXECUTE permission on SCHEMAs that the
user needs to query
"Bart" <b@.sdq.dc> wrote in message
news:u1ddKM3bHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Well, i want to create a new login for account ASPNET (which runs under
> ASP.NET) and then define an user (aspnet) for 'mydatabase'. That user
> must get read/write prights to the db. At that level, i see in the window
> configuration two things about read /write: Owned Schema and Role members.
> So my question is: should i take db_readreader / db_writer in the Ownd
> Schema or in Role members or in both?
> Thanks
> "Uri Dimant" <urid@.iscar.co.il> schreef in bericht
> news:%23p8We%232bHHA.4632@.TK2MSFTNGP03.phx.gbl...
>|||"Uri Dimant" <urid@.iscar.co.il> schreef in bericht
news:OOCCsw3bHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Bart
> Does the user should run queries which manipulate with tables belong to
> anohter SCHEMA?
> I'd go with ROLES and grant SELECT/EXECUTE permission on SCHEMAs that the
> user needs to query
Thanks, but I still don't understand the difference between giving the Role
db_datareader / db_datawriter to user 'aspnet' and adding user 'aspnet' to
Schema dbdatareader and Schema db_datawriter ...|||Bart (b@.sdq.dc) writes:
> Thanks, but I still don't understand the difference between giving the
> Role db_datareader / db_datawriter to user 'aspnet' and adding user
> 'aspnet' to Schema dbdatareader and Schema db_datawriter ...
You should not need the schemss. They exist of legacy reasons. In SQL 2000
there was no difference between a role/user on the one hand and a schema
on the other. If you created a user/role X, you also got a schema X included
in the price.
In SQL 2005 schemas and database principals (users and roles) are separated.
But Microsoft still by default creates schemas for all pre-defined roles
and users, since old applications may rely on these and create objects
in these schemas.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||ok, thanks
"Erland Sommarskog" <esquel@.sommarskog.se> schreef in bericht
news:Xns99004B34D29Yazorman@.127.0.0.1...
> Bart (b@.sdq.dc) writes:
> You should not need the schemss. They exist of legacy reasons. In SQL 2000
> there was no difference between a role/user on the one hand and a schema
> on the other. If you created a user/role X, you also got a schema X
> included
> in the price.
> In SQL 2005 schemas and database principals (users and roles) are
> separated.
> But Microsoft still by default creates schemas for all pre-defined roles
> and users, since old applications may rely on these and create objects
> in these schemas.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

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
> >
> >
>