deAttach database: databases

Showing posts with label databases. Show all posts

Saturday, February 25, 2012

DBs not appearing in query analyer dropdown

A couple of databases on one of our SQL Servers is missing in the db
dropdown in query analyzer.

Does anyone know what I need to update to sync this with the actual
databases?

Thanks,

BurtThe user you are logged in with does not have access to those
databases. There is no "syncing" necessary.

DBREINDEX at threshold for all databases

There is a proc in Books Online that allows you to execute a INDEXDEFRAG on
all indexes in a database that have a logical fragmentation percentage above
a specific limit. IT useds SHOWCONTIG and a temporary table. I want to run
this proc with DBREINDEX instead and I want to schedule it w

ly for all
exising user databases. (The Database Maintenance Wizard is too
inefficient.) If a new database gets added to the environment, I want the
proc to dynamically pick up that new database.
The problem is that this proc has to be executed within the database to be
defragged. I'm having trouble modifying it to loop for each existing
database.
I've tried some "EXEC ('USE ' + @.dbname + ' DBCC SHOW..." but am still
having some problems. This is probably a pretty basic type of maintenance
procedure. Does anyone already have this coded that they would share?I actually have something that may work for you - but not without a little
work. I was trying to do the same thing - a w

ly job that would run on an
y
existing user db's. If you run this, it will pick up all user databases. I
use PRINT @.SQL instead of EXEC because I was unable to get it to execute the
DBREINDEX without error. And right now I'm taking the results of that and
driving the job. If you're able to get around that, please advise.
DECLARE @.SQL NVarchar(4000)
SET @.SQL = ''
SELECT @.SQL = @.SQL + 'EXEC ' + NAME + '..sp_MSforeachtable @.command1=''DBCC
DBREINDEX (''''*'''')'', @.replacechar=''*''' + Char(13)
FROM MASTER..Sysdatabases
WHERE dbid >6
PRINT @.SQL
-- Lynn
"Stephanie" wrote:

> There is a proc in Books Online that allows you to execute a INDEXDEFRAG o
n
> all indexes in a database that have a logical fragmentation percentage abo
ve
> a specific limit. IT useds SHOWCONTIG and a temporary table. I want to r
un
> this proc with DBREINDEX instead and I want to schedule it w

ly for all
> exising user databases. (The Database Maintenance Wizard is too
> inefficient.) If a new database gets added to the environment, I want the
> proc to dynamically pick up that new database.
> The problem is that this proc has to be executed within the database to be
> defragged. I'm having trouble modifying it to loop for each existing
> database.
> I've tried some "EXEC ('USE ' + @.dbname + ' DBCC SHOW..." but am still
> having some problems. This is probably a pretty basic type of maintenance
> procedure. Does anyone already have this coded that they would share?
>|||Maybe this helps:
http://milambda.blogspot.com/2005/0...in-current.html
It needs a wrapper that will execute it for each database, and you need to
propagate the db_id value through to the dbcc call (currently the value of
db_id is 0 - current database).
ML

Friday, February 24, 2012

dbowner no more dbower (SID lost)

hello eveybody,
I have an Sql server 2000 installed with several databases.
this instance is configured with both sql server identification and
Windows ID
I have created a login called john and this login has a default
database mydb (sp_addlogin)
On this default database, i have granted the Access to this login
(sp_grantdbaccess)
a new application has created a new database newdb and, for
differents reasons, the program has created again the john login
(same name)
Now, I can Access with this login to the database newdb but no more
to the default one mydb .
When I say i can Access... that means :
Select * from toto is working on newdb
(I dont need to write Select * from john.toto)
But with mydb ,
Select * from toto does not work,
I need to write the select this way : Select * from john.toto
Since I use an application, I cannot change the sql statement
since john is "dbowner" and has a defaut db "mydb", this is strange
When I read master.dbo.sysusers, the user john has a complexe SID
( 0x93F921DB3... )
When i read master.dbo.sysdatabases :
The new database newdb has the same complexe SID ( 0x93F921DB3...
)
The old database mydb has lost the SID witch is replaced with 0x01
The table toto has the correct UID that is john in sysusers table
How can I reset "john" owner on all tables from mydb
many thanks
nicolasIt looks like someone has changed the owner of the old database to be sa
(sid = 0x01). You can execute sp_changedbowner to reset the database owner
to be John.
See
http://msdn.microsoft.com/library/d...>
cz_30s2.asp,
for more information on sp_changedbowner.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"906507N" <906507N@.10090.I20034> wrote in message
news:Xns980E2BCBAB870140507NA02619@.207.46.248.16...
> hello eveybody,
> I have an Sql server 2000 installed with several databases.
> this instance is configured with both sql server identification and
> Windows ID
> I have created a login called john and this login has a default
> database mydb (sp_addlogin)
> On this default database, i have granted the Access to this login
> (sp_grantdbaccess)
>
> a new application has created a new database newdb and, for
> differents reasons, the program has created again the john login
> (same name)
> Now, I can Access with this login to the database newdb but no more
> to the default one mydb .
> When I say i can Access... that means :
> Select * from toto is working on newdb
> (I dont need to write Select * from john.toto)
> But with mydb ,
> Select * from toto does not work,
> I need to write the select this way : Select * from john.toto
> Since I use an application, I cannot change the sql statement
> since john is "dbowner" and has a defaut db "mydb", this is strange
> When I read master.dbo.sysusers, the user john has a complexe SID
> ( 0x93F921DB3... )
> When i read master.dbo.sysdatabases :
> The new database newdb has the same complexe SID ( 0x93F921DB3...
> )
> The old database mydb has lost the SID witch is replaced with 0x01
>
> The table toto has the correct UID that is john in sysusers table
> How can I reset "john" owner on all tables from mydb
> many thanks
> nicolas
>|||many thanks for your help
I try the command but sql server say :
Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
The proposed new database owner is already a user in the database
I'm connected with the sa account into the default database of the owner
"Laurentiu Cristofor [MSFT]" <laur@.nospam.com> wrote in
news:ewzvm2dsGHA.3496@.TK2MSFTNGP06.phx.gbl:

> It looks like someone has changed the owner of the old database to be
> sa (sid = 0x01). You can execute sp_changedbowner to reset the
> database owner to be John.
> See
> http://msdn.microsoft.com/library/d...ry/en-us/tsqlre
> f/ts_sp_ca-cz_30s2.asp, for more information on sp_changedbowner.
> Thanks
>|||Try creating a temporary login, change the database to be
owned by that temp login. Then change the database to be
owned by John and drop the temp login. Something like:
use YourDatabase
go
exec sp_addlogin 'TempLogin'
exec sp_changedbowner 'TempLogin'
exec sp_changedbowner 'John'
exec sp_droplogin 'TempLogin'
-Sue
On Thu, 27 Jul 2006 18:00:28 -0700, 906507N
<906507N@.10090.I20034> wrote:
[vbcol=seagreen]
>many thanks for your help
>I try the command but sql server say :
>Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
> The proposed new database owner is already a user in the database
>
>I'm connected with the sa account into the default database of the owner
>
>"Laurentiu Cristofor [MSFT]" <laur@.nospam.com> wrote in
>news:ewzvm2dsGHA.3496@.TK2MSFTNGP06.phx.gbl:
>|||If this error comes as a result of attempting to make the owner to be
"John", then it means that while the owner was sa, a user was already
created for John in the database. If you now want to make John the database
owner, you should remove that user:
sp_dropuser 'John'
and then you should be able to call
sp_changedbowner 'John'
There is a catch: if 'John' owns objects in the database, you cannot drop
him, so you will have to change the ownership of those objects to someone
else first, by using sp_changeobjectowner.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"906507N" <906507N@.10090.I20034> wrote in message
news:Xns980E1E98F3B4D0140507NA02619@.207.46.248.16...
> many thanks for your help
> I try the command but sql server say :
> Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
> The proposed new database owner is already a user in the database
>
> I'm connected with the sa account into the default database of the owner
>
> "Laurentiu Cristofor [MSFT]" <laur@.nospam.com> wrote in
> news:ewzvm2dsGHA.3496@.TK2MSFTNGP06.phx.gbl:
>
>|||906507N <906507N@.10090.I20034> wrote in
news:Xns980E1E98F3B4D0140507NA02619@.207.46.248.16:

> many thanks for your help
> I try the command but sql server say :
> Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line
47
> The proposed new database owner is already a user in the database
>
> I'm connected with the sa account into the default database of the
owner
>
> "Laurentiu Cristofor [MSFT]" <laur@.nospam.com> wrote in
> news:ewzvm2dsGHA.3496@.TK2MSFTNGP06.phx.gbl:
>
us/tsqlre[vbcol=seagreen]
>
sue and Laurentiu,
many thanks for your help
The solution was here
I created a new login, changeobjectowner for each database tables from
old user,delete and create again the old login, changeobjectowner back to
the old user all objects
now, it is correct
again, many thanks for your answers.
nicolas

dbo with user name in "users"

Using SS2000 SP4. Under "Users" in EM, I see "dbo" in the name column and in
the login name column a name like "webapp". In other databases, I see "dbo"
in the name column and nothing in the login name column.
What is the significance of having another user like "webapp" in the login
name column? And how could can I assign another user login to dbo?
Thanks,
--
Dan D.Dan
DBO is a priviliged user. There are difference between LOGIN and USER.
BOL has pertty good article about it
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:C8FC2930-1CC2-4BEF-BACA-C292396E498E@.microsoft.com...
> Using SS2000 SP4. Under "Users" in EM, I see "dbo" in the name column and
> in
> the login name column a name like "webapp". In other databases, I see
> "dbo"
> in the name column and nothing in the login name column.
> What is the significance of having another user like "webapp" in the login
> name column? And how could can I assign another user login to dbo?
> Thanks,
> --
> Dan D.|||I've read BOL but I don't see anything that explains how a user ends up in
the "login name" column for "dbo" in the "users" part of EM. Sometimes "sa"
is in the "login name" column for "dbo", sometimes the "login name" column
for "dbo" is empty and sometimes, there is another user name in the "login
name" column for "dbo".
--
Dan D.
"Uri Dimant" wrote:

> Dan
> DBO is a priviliged user. There are difference between LOGIN and USER.
> BOL has pertty good article about it
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:C8FC2930-1CC2-4BEF-BACA-C292396E498E@.microsoft.com...
>
>|||Dan
You can add user to db_owner database fixed role and all objects will be
created as dbo.tablename ( If he/she is a member of sysadmin server role)
If he/she isn't you needs to specify
create table dbo.table (col1 int)
Waht does that mean "sometimes"? Can you reproduce the problem?
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:913FEB8F-7EE7-4161-8EBA-54D71DBA6314@.microsoft.com...[vbcol=seagreen]
> I've read BOL but I don't see anything that explains how a user ends up in
> the "login name" column for "dbo" in the "users" part of EM. Sometimes
> "sa"
> is in the "login name" column for "dbo", sometimes the "login name" column
> for "dbo" is empty and sometimes, there is another user name in the "login
> name" column for "dbo".
> --
> Dan D.
>
> "Uri Dimant" wrote:
>|||"Uri Dimant" wrote:

> Dan
> You can add user to db_owner database fixed role and all objects will be
> created as dbo.tablename ( If he/she is a member of sysadmin server role)
> If he/she isn't you needs to specify
> create table dbo.table (col1 int)
This I understand.

> Waht does that mean "sometimes"? Can you reproduce the problem?
If I create a new database, my name will show up in the "login name" column
for "dbo". That would explain some of what I see. But as I look through
different databases I see a lot of different users in the "login name" colum
n
for "dbo" in the "login" column. In one database, the "login name" column fo
r
"dbo" will be empty. In another database, a user called "jbanner" will be in
the "login name" column for "dbo". In another database, "sa" will be in the
"login name" column for "dbo". In another database "domain\tbrown" will be i
n
the "login name" column for "dbo".
Some of these users are used for applications only, and it's highly unlikely
that someone logged on as the user and created a database so there must be
some other way that it happens.
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:913FEB8F-7EE7-4161-8EBA-54D71DBA6314@.microsoft.com...
>
>|||Hi Dan
The login name column should show the login name who is considered the owner
of the database, whose user name will be 'dbo' when they use the database. I
don't know why EM is sometimes showing a blank here. There should always be
a mapped login name. One guess is that the login of the owner is a domain
account, and the domain is not available to validate the name. You can try
to verify that by run sp_helpdb in a query window, and seeing what gets
listed for the owner of the database.
You can change the login name that owns a database by using a query window.
Use the database, and run the following:
EXEC sp_changedbowner '<new_owner_login_name>'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:C8FC2930-1CC2-4BEF-BACA-C292396E498E@.microsoft.com...
> Using SS2000 SP4. Under "Users" in EM, I see "dbo" in the name column and
> in
> the login name column a name like "webapp". In other databases, I see
> "dbo"
> in the name column and nothing in the login name column.
> What is the significance of having another user like "webapp" in the login
> name column? And how could can I assign another user login to dbo?
> Thanks,
> --
> Dan D.|||When I run sp_helpdb on the database with a blank in the "login name" column
for dbo, I get an error: "cannot insert the value NULL into column '', table
''; column does not allow nulls. INSERT fails. I changed the dbowner to 'sa'
and it then worked correctly.
The other databases respond appropriately.
I do have some databases with a domain user as owner so I don't think the
domain is the problem.
In a database for an application, could I assign the database owner as a
user with limited (read/write) rights? Or would making the user the database
owner override the read/write permissions?
Thanks,
--
Dan D.
"Kalen Delaney" wrote:

> Hi Dan
> The login name column should show the login name who is considered the own
er
> of the database, whose user name will be 'dbo' when they use the database.
I
> don't know why EM is sometimes showing a blank here. There should always b
e
> a mapped login name. One guess is that the login of the owner is a domain
> account, and the domain is not available to validate the name. You can try
> to verify that by run sp_helpdb in a query window, and seeing what gets
> listed for the owner of the database.
> You can change the login name that owns a database by using a query window
.
> Use the database, and run the following:
> EXEC sp_changedbowner '<new_owner_login_name>'
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:C8FC2930-1CC2-4BEF-BACA-C292396E498E@.microsoft.com...
>
>|||Dan
I have seen that problem with the NULL error message, and I still believe it
has something to do with not being able to validate the login. Other domain
logins might be able to be validated, or might be using cached credentials,
or something. But as you found, changing the owner to sa is usually a great
solution.
The owner of a database always has the user name dbo in the database, which
always give her full permissions. If you don't want that login to have full
permission, give ownership of the database to someone else.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:841A0E2F-8DBE-437F-8661-2CFF7998404E@.microsoft.com...[vbcol=seagreen]
> When I run sp_helpdb on the database with a blank in the "login name"
> column
> for dbo, I get an error: "cannot insert the value NULL into column '',
> table
> ''; column does not allow nulls. INSERT fails. I changed the dbowner to
> 'sa'
> and it then worked correctly.
> The other databases respond appropriately.
> I do have some databases with a domain user as owner so I don't think the
> domain is the problem.
> In a database for an application, could I assign the database owner as a
> user with limited (read/write) rights? Or would making the user the
> database
> owner override the read/write permissions?
> Thanks,
> --
> Dan D.
>
> "Kalen Delaney" wrote:
>|||We've had some developers leave recently and I removed their logins. It migh
t
be that one of them owned the database and because the login/user has been
removed the system can't validate them anymore. I thought the system always
asked to reassign the object in that case but maybe not.
Thanks Kalen,
--
Dan D.
"Kalen Delaney" wrote:

> Dan
> I have seen that problem with the NULL error message, and I still believe
it
> has something to do with not being able to validate the login. Other domai
n
> logins might be able to be validated, or might be using cached credentials
,
> or something. But as you found, changing the owner to sa is usually a grea
t
> solution.
> The owner of a database always has the user name dbo in the database, whic
h
> always give her full permissions. If you don't want that login to have ful
l
> permission, give ownership of the database to someone else.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:841A0E2F-8DBE-437F-8661-2CFF7998404E@.microsoft.com...
>
>|||You can identify databases invalid owners with the following query:
SELECT name
FROM master..sysdatabases
WHERE SUSER_SNAME(sid) IS NULL
Execute sp_changedbowner for the problem databases.

> We've had some developers leave recently and I removed their logins. It
> might
> be that one of them owned the database and because the login/user has been
> removed the system can't validate them anymore. I thought the system
> always
> asked to reassign the object in that case but maybe not.
The check for database ownership is done when you drop a standard login
(sp_droplogin) but not when you remove Windows logins (sp_revokelogin). In
fact, you can have a Windows login own a database even when no corresponding
login exists in SQL Server. This is often the case when an administrator
connects to SQL Server via BUILTIJN\Administrators group membership and then
creates, restores or attaches a database. The database is then owned by the
individual's Windows account. However, the database owner becomes invalid
if the Windows account is later deleted.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:9B68CC18-5C0F-47E0-BB0E-276A7C92569A@.microsoft.com...[vbcol=seagreen]
> We've had some developers leave recently and I removed their logins. It
> might
> be that one of them owned the database and because the login/user has been
> removed the system can't validate them anymore. I thought the system
> always
> asked to reassign the object in that case but maybe not.
> Thanks Kalen,
> --
> Dan D.
>
> "Kalen Delaney" wrote:
>

DBO user missing in two databases

SQL 2K on Windows 2003
I know the documentation says that the dbo user account in each database can
't be deleted, but it has gone missing overnight on two databases. This has
caused my SA account to hose since it references the dbo user for these two
db's.
I have failed so far at creating a dbo user account for the sa login. It gi
ves me error 15405:Cannot use the reserved user or role name 'sa'.
I have also not been successful at creating the dbo user account with a diff
erent system administrator login. It gives me the error 15023:user or role
'dbo' already exists in the current database.
I'm stuck and could really use some suggestions.
Thanks in advance,
unsophisticated - small time dba
ShirleyTo change the owner of the databases simply use sp_changedbowner e.g.
use <your db name>
go
exec sp_changedbowner 'sa','true'
go
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ShirleyL" <shirleyNoSpamL@.abshernw.com> wrote in message
news:uUSwC1PJFHA.1096@.tk2msftngp13.phx.gbl...
SQL 2K on Windows 2003
I know the documentation says that the dbo user account in each database
can't be deleted, but it has gone missing overnight on two databases. This
has caused my SA account to hose since it references the dbo user for these
two db's.
I have failed so far at creating a dbo user account for the sa login. It
gives me error 15405:Cannot use the reserved user or role name 'sa'.
I have also not been successful at creating the dbo user account with a
different system administrator login. It gives me the error 15023:user or
role 'dbo' already exists in the current database.
I'm stuck and could really use some suggestions.
Thanks in advance,
unsophisticated - small time dba
Shirley|||Jasper Smith wrote:
> To change the owner of the databases simply use sp_changedbowner e.g.
> use <your db name>
> go
> exec sp_changedbowner 'sa','true'
> go
>
There is a small and totally insignificant danger in using sa as a database
owner. When cross database ownership chaining for the database or the server
is activated, and there are non sysadmins in the database with dbo_owner
rights, then the can create stored procedure which access sp's in the master
database like sp_addsrvrolemember.
Hans

Sunday, February 19, 2012

dbo question

Hi
I can see that in some of our databases, the user 'dbo' are mapped to a
specific loginname where in others it's not mapped to anything. How can I
change this so the 'dbo' user isn't mapped to a specific useraccount. The
reason is that I'd like to remove the login that's set as 'dbo' for those
databases.
I've looked in BOL, but I'm not quite sure about the steps to perform to get
this fixed. I know that all members of the Sysadmin role is mapped to 'dbo'
so that's what I'd like to have rather than mapping a specific user to the
'dbo' user.
Since it's in our production environment is has to be changed, I'd like to
be fairly sure about the steps to do before I just go ahead and change
it...;-)
Regards
SteenHello Steen,
You can use sp_changedbowner like this:
use <mydb>
exec sp_changedbowner 'sa'
This will map the sa login to dbo user in the mydb database. Why do you want
to remove the dbo user?
Mark.
> Hi
> I can see that in some of our databases, the user 'dbo' are mapped to
> a specific loginname where in others it's not mapped to anything. How
> can I change this so the 'dbo' user isn't mapped to a specific
> useraccount. The reason is that I'd like to remove the login that's
> set as 'dbo' for those databases.
> I've looked in BOL, but I'm not quite sure about the steps to perform
> to get this fixed. I know that all members of the Sysadmin role is
> mapped to 'dbo' so that's what I'd like to have rather than mapping a
> specific user to the 'dbo' user.
> Since it's in our production environment is has to be changed, I'd
> like to be fairly sure about the steps to do before I just go ahead
> and change it...;-)
> Regards
> Steen|||Hi Mark
The reason for removing this specific dbo user, is because we are trying to
"clean up" our SQL server installations. We have 8 servers for various
applications and there haven't really been any corporate guidelines for how
to install them. They are therefore installed and configured in different
ways.
As I understand the sp_changedbowner, it will map the username spcified to
the dbo role, but I'm not sure that's what I want.
If I look at some of the other SQLServers, it looks like no specific
loginname has been mapped to 'dbo'. In this case I assume that dbo is the
loginnames which are members of the System Administrators Role on the
server. I don't know if this scenario is advisable or not, but would it be
possible to remove the dbo mapping to a specific loginname and change it to
a "blank" loginname?
/Steen
Mark Allison wrote:
> Hello Steen,
> You can use sp_changedbowner like this:
> use <mydb>
> exec sp_changedbowner 'sa'
> This will map the sa login to dbo user in the mydb database. Why do
> you want to remove the dbo user?
> Mark.
>
>> Hi
>> I can see that in some of our databases, the user 'dbo' are mapped to
>> a specific loginname where in others it's not mapped to anything. How
>> can I change this so the 'dbo' user isn't mapped to a specific
>> useraccount. The reason is that I'd like to remove the login that's
>> set as 'dbo' for those databases.
>> I've looked in BOL, but I'm not quite sure about the steps to perform
>> to get this fixed. I know that all members of the Sysadmin role is
>> mapped to 'dbo' so that's what I'd like to have rather than mapping a
>> specific user to the 'dbo' user.
>> Since it's in our production environment is has to be changed, I'd
>> like to be fairly sure about the steps to do before I just go ahead
>> and change it...;-)
>> Regards
>> Steen|||dbo is always mapped to something. It is safe to map all of them to sa or
some other login you expect will always exist... I use either sa or a
trusted login so moving databases arouns isn't quite so much trouble.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:u36YSpI$EHA.612@.TK2MSFTNGP09.phx.gbl...
> Hi Mark
> The reason for removing this specific dbo user, is because we are trying
to
> "clean up" our SQL server installations. We have 8 servers for various
> applications and there haven't really been any corporate guidelines for
how
> to install them. They are therefore installed and configured in different
> ways.
> As I understand the sp_changedbowner, it will map the username spcified to
> the dbo role, but I'm not sure that's what I want.
> If I look at some of the other SQLServers, it looks like no specific
> loginname has been mapped to 'dbo'. In this case I assume that dbo is the
> loginnames which are members of the System Administrators Role on the
> server. I don't know if this scenario is advisable or not, but would it be
> possible to remove the dbo mapping to a specific loginname and change it
to
> a "blank" loginname?
> /Steen
>
> Mark Allison wrote:
> > Hello Steen,
> >
> > You can use sp_changedbowner like this:
> >
> > use <mydb>
> > exec sp_changedbowner 'sa'
> >
> > This will map the sa login to dbo user in the mydb database. Why do
> > you want to remove the dbo user?
> >
> > Mark.
> >
> >
> >> Hi
> >>
> >> I can see that in some of our databases, the user 'dbo' are mapped to
> >> a specific loginname where in others it's not mapped to anything. How
> >> can I change this so the 'dbo' user isn't mapped to a specific
> >> useraccount. The reason is that I'd like to remove the login that's
> >> set as 'dbo' for those databases.
> >>
> >> I've looked in BOL, but I'm not quite sure about the steps to perform
> >> to get this fixed. I know that all members of the Sysadmin role is
> >> mapped to 'dbo' so that's what I'd like to have rather than mapping a
> >> specific user to the 'dbo' user.
> >>
> >> Since it's in our production environment is has to be changed, I'd
> >> like to be fairly sure about the steps to do before I just go ahead
> >> and change it...;-)
> >>
> >> Regards
> >> Steen
>|||Thanks Wayne,
I investigated it a little bit further, and I think I found out what
confused me. When I look at the dbo user for a database in EM, it shows no
Login Name. If I then look at the same db in QA (sp_helpdb) it shows the
owner ok.
The cases where EM doesn't show a login name, seems to be the ones where the
dbo isn't mapped to a login name that has been created as a login in SQL
but is a user that's member of the SystemAdministrator role (and/or in the
local admin groups on the server). This might be obvious and clear for
others, but it confused me a little bit...;-).
Thanks for your inputs.
Regards
Steen
Wayne Snyder wrote:
> dbo is always mapped to something. It is safe to map all of them to
> sa or some other login you expect will always exist... I use either
> sa or a trusted login so moving databases arouns isn't quite so much
> trouble.
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:u36YSpI$EHA.612@.TK2MSFTNGP09.phx.gbl...
>> Hi Mark
>> The reason for removing this specific dbo user, is because we are
>> trying to "clean up" our SQL server installations. We have 8 servers
>> for various applications and there haven't really been any corporate
>> guidelines for how to install them. They are therefore installed and
>> configured in different ways.
>> As I understand the sp_changedbowner, it will map the username
>> spcified to the dbo role, but I'm not sure that's what I want.
>> If I look at some of the other SQLServers, it looks like no specific
>> loginname has been mapped to 'dbo'. In this case I assume that dbo
>> is the loginnames which are members of the System Administrators
>> Role on the server. I don't know if this scenario is advisable or
>> not, but would it be possible to remove the dbo mapping to a
>> specific loginname and change it to a "blank" loginname?
>> /Steen
>>
>> Mark Allison wrote:
>> Hello Steen,
>> You can use sp_changedbowner like this:
>> use <mydb>
>> exec sp_changedbowner 'sa'
>> This will map the sa login to dbo user in the mydb database. Why do
>> you want to remove the dbo user?
>> Mark.
>>
>> Hi
>> I can see that in some of our databases, the user 'dbo' are mapped
>> to a specific loginname where in others it's not mapped to
>> anything. How can I change this so the 'dbo' user isn't mapped to
>> a specific useraccount. The reason is that I'd like to remove the
>> login that's set as 'dbo' for those databases.
>> I've looked in BOL, but I'm not quite sure about the steps to
>> perform to get this fixed. I know that all members of the Sysadmin
>> role is mapped to 'dbo' so that's what I'd like to have rather
>> than mapping a specific user to the 'dbo' user.
>> Since it's in our production environment is has to be changed, I'd
>> like to be fairly sure about the steps to do before I just go ahead
>> and change it...;-)
>> Regards
>> Steen

dbo login name wrong after copy

Just copied all databases, from Windows NT4 with SQL 7 in a domain to a new Windows 2000 with SQL 2000 in workgroup, with the SQL 2000 copy database wizard.
Now a few databases still have a dbo with login name DOMAINNAME/Administrator while this domain doesn't exist anymore in a few weeks from now
1. Can I get a problem when I don't resolve this
2. Is there a way to give the dbo the sa login name"Nico" <anonymous@.discussions.microsoft.com> wrote in message
news:84E0FEA3-50AB-400F-981A-9C53951002E5@.microsoft.com...
> Just copied all databases, from Windows NT4 with SQL 7 in a domain to a
new Windows 2000 with SQL 2000 in workgroup, with the SQL 2000 copy database
wizard.
> Now a few databases still have a dbo with login name
DOMAINNAME/Administrator while this domain doesn't exist anymore in a few
weeks from now.
> 1. Can I get a problem when I don't resolve this?
> 2. Is there a way to give the dbo the sa login name?
First off, 'dbo' is a database role, not a user. A role is like a group in
Windows. Anyone you assign to the dbo role in a database can do anything
they want in that database.
'sysadmin' is a server role that can do anything anywhere in your SQL server
(and so is like a super dbo, if you like). By default, the local admin
group and the user sa are both assigned to the sysadmin role, and so can
both act as the dbo of a database (plus additional actions). So there is no
need to add sa to the dbo as it is already more powerful than dbo.
Any other user that you want to have dbo access just add them to the
appropriate database's dbo role. If your DOMAIN/Administrator is going,
then leaving the login there won't be a problem, as no-one will be able to
use it (though for completeness you might want to remove it). You can add
as many other accounts as you feel is safe to the dbo role.
Bob
--
Warning: Do not look into the light sabre whilst switching it on
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.541 / Virus Database: 335 - Release Date: 14/11/2003|||> First off, 'dbo' is a database role, not a user.
Actually 'dbo' is a database user and is required in every SQL Server
database. Unlike other database users, the login mapping for the 'dbo'
user is determined by database ownership.
This should not be confused with user membership of the db_owner fixed
database role. Members of the db_owner role have similar permissions as
the 'dbo' role but are not mapped to the 'dbo' user. Members of the
sysadmin server role automatically impersonate the 'dbo' user in all
databases, even though their login is not necessarily mapped to the
'dbo' user.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Bob Simms" <bob_simms@.somewhere.com> wrote in message
news:NjItb.1924$1u4.548@.news-binary.blueyonder.co.uk...
> "Nico" <anonymous@.discussions.microsoft.com> wrote in message
> news:84E0FEA3-50AB-400F-981A-9C53951002E5@.microsoft.com...
> > Just copied all databases, from Windows NT4 with SQL 7 in a domain
to a
> new Windows 2000 with SQL 2000 in workgroup, with the SQL 2000 copy
database
> wizard.
> > Now a few databases still have a dbo with login name
> DOMAINNAME/Administrator while this domain doesn't exist anymore in a
few
> weeks from now.
> > 1. Can I get a problem when I don't resolve this?
> > 2. Is there a way to give the dbo the sa login name?
> First off, 'dbo' is a database role, not a user. A role is like a
group in
> Windows. Anyone you assign to the dbo role in a database can do
anything
> they want in that database.
> 'sysadmin' is a server role that can do anything anywhere in your SQL
server
> (and so is like a super dbo, if you like). By default, the local
admin
> group and the user sa are both assigned to the sysadmin role, and so
can
> both act as the dbo of a database (plus additional actions). So there
is no
> need to add sa to the dbo as it is already more powerful than dbo.
> Any other user that you want to have dbo access just add them to the
> appropriate database's dbo role. If your DOMAIN/Administrator is
going,
> then leaving the login there won't be a problem, as no-one will be
able to
> use it (though for completeness you might want to remove it). You can
add
> as many other accounts as you feel is safe to the dbo role.
> Bob
> --
> Warning: Do not look into the light sabre whilst switching it on
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.541 / Virus Database: 335 - Release Date: 14/11/2003
>|||You can change database ownership to 'sa' with sp_changedbowner:
USE MyDatabase
EXEC sp_changedbowner 'sa'
GO
If you're not running SQL 2000 SP3, you may get an erroneous error
stating that the proposed owner is already a user in the database. In
that case, temporarily change ownership to a non-conflicting login:
USE MyDatabase
EXEC sp_addlogin 'TempOwner'
EXEC sp_changedbowner 'TempOwner'
EXEC sp_changedbowner 'sa'
EXEC sp_droplogin 'TempOwner'
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Nico" <anonymous@.discussions.microsoft.com> wrote in message
news:84E0FEA3-50AB-400F-981A-9C53951002E5@.microsoft.com...
> Just copied all databases, from Windows NT4 with SQL 7 in a domain to
a new Windows 2000 with SQL 2000 in workgroup, with the SQL 2000 copy
database wizard.
> Now a few databases still have a dbo with login name
DOMAINNAME/Administrator while this domain doesn't exist anymore in a
few weeks from now.
> 1. Can I get a problem when I don't resolve this?
> 2. Is there a way to give the dbo the sa login name?
>|||Ok. I've changed the dbowner to sa. No problem when I look at the properties of the database.
Still I've got the user dbo with login DOMAINNAME/Administrator. Is there a possibility to change the dbo login to sa?|||sp_change_users_login?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Nico" <nvk@.galvano.nl> wrote in message news:A5285328-1CBE-47E9-BC5F-428460D9F8FC@.microsoft.com...
> Ok. I've changed the dbowner to sa. No problem when I look at the properties of the database.
> Still I've got the user dbo with login DOMAINNAME/Administrator. Is there a possibility to change
the dbo login to sa?|||Tried this before:
dbo is a forbidden value.
See also books online says sa and dbo cannot be used.
Another suggestion?|||How did you change the owner? sp_changedbowner?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Nico" <anonymous@.discussions.microsoft.com> wrote in message
news:0E3B442E-0722-4D65-9E54-2DC90AE18C29@.microsoft.com...
> Tried this before:
> dbo is a forbidden value.
> See also books online says sa and dbo cannot be used.
> Another suggestion?|||Perhaps this is an Enterprise Manager refresh issue. Does sp_helpdb
'MyDatabase' return the correct owner?
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Nico" <nvk@.galvano.nl> wrote in message
news:A5285328-1CBE-47E9-BC5F-428460D9F8FC@.microsoft.com...
> Ok. I've changed the dbowner to sa. No problem when I look at the
properties of the database.
> Still I've got the user dbo with login DOMAINNAME/Administrator. Is
there a possibility to change the dbo login to sa?|||Yes, this works fine when I look at the database properties
Just found out a procedure to solve the problem
1. detach database (Query Analyser
2. copy mdf and ldf files (Explorer
3. create database (Query Analyser
4. Delete database (Enterprise Manager
5. copy back mdf and ldf files (Explorer
6. attach database (Query Analyser
Now the dbo user has login name of sa (login name Query Analyser when created database).

dbo does not have a login name..

Hi
In one of the databases, dbo users under Users option does not have the
login name.Is there any way to assign a login name "sa" to dbo.
Thanks and Regards
Jagminder
sp_changedbowner
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jagminder Chugh" <JagminderChugh@.discussions.microsoft.com> wrote in message
news:3250D5AC-F964-4BD7-8C09-C0773AD46721@.microsoft.com...
> Hi
> In one of the databases, dbo users under Users option does not have the
> login name.Is there any way to assign a login name "sa" to dbo.
> Thanks and Regards
> Jagminder
|||when i try that it says,
Cannot change the owner of the master database.
"Tibor Karaszi" wrote:

> sp_changedbowner
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jagminder Chugh" <JagminderChugh@.discussions.microsoft.com> wrote in message
> news:3250D5AC-F964-4BD7-8C09-C0773AD46721@.microsoft.com...
>
>
|||Are you saying that it is the *master* database that doesn't have an owner? That shouldn't be
possible. The proc changes the owner for the current database, so you need to USE the database you
want to change owner for first. See Books Online for details on how to use this procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jagminder Chugh" <JagminderChugh@.discussions.microsoft.com> wrote in message
news:336DF028-34FB-431C-8739-C35C9FBF7460@.microsoft.com...[vbcol=seagreen]
> when i try that it says,
> Cannot change the owner of the master database.
> "Tibor Karaszi" wrote:
|||hey tibor, your site is down..
|||Thanks. It was down for a while, but I can reach it now...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mb" <mb@.discussions.microsoft.com> wrote in message
news:CE44A7CB-CD22-41D7-991D-91817BE5D28F@.microsoft.com...
> hey tibor, your site is down..
>
|||Usually that happens when you restore databases from different servers.
Anyway, try running sp_change_users_login. You can find it in the BOL.
Lionel Chacon
"Jagminder Chugh" wrote:

> Hi
> In one of the databases, dbo users under Users option does not have the
> login name.Is there any way to assign a login name "sa" to dbo.
> Thanks and Regards
> Jagminder
|||sp_change_users_login doesn't work for the owner of the database. For that you need to use
sp_changedbowner.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lionel Chacon" <LionelChacon@.discussions.microsoft.com> wrote in message
news:5C767497-923B-4041-9A25-787C6D5A4801@.microsoft.com...[vbcol=seagreen]
> Usually that happens when you restore databases from different servers.
> Anyway, try running sp_change_users_login. You can find it in the BOL.
> --
> Lionel Chacon
>
> "Jagminder Chugh" wrote:

dbo does not have a login name..

Hi
In one of the databases, dbo users under Users option does not have the
login name.Is there any way to assign a login name "sa" to dbo.
Thanks and Regards
Jagmindersp_changedbowner
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jagminder Chugh" <JagminderChugh@.discussions.microsoft.com> wrote in message
news:3250D5AC-F964-4BD7-8C09-C0773AD46721@.microsoft.com...
> Hi
> In one of the databases, dbo users under Users option does not have the
> login name.Is there any way to assign a login name "sa" to dbo.
> Thanks and Regards
> Jagminder|||when i try that it says,
Cannot change the owner of the master database.
"Tibor Karaszi" wrote:
> sp_changedbowner
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jagminder Chugh" <JagminderChugh@.discussions.microsoft.com> wrote in message
> news:3250D5AC-F964-4BD7-8C09-C0773AD46721@.microsoft.com...
> > Hi
> > In one of the databases, dbo users under Users option does not have the
> > login name.Is there any way to assign a login name "sa" to dbo.
> >
> > Thanks and Regards
> > Jagminder
>
>|||Are you saying that it is the *master* database that doesn't have an owner? That shouldn't be
possible. The proc changes the owner for the current database, so you need to USE the database you
want to change owner for first. See Books Online for details on how to use this procedure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jagminder Chugh" <JagminderChugh@.discussions.microsoft.com> wrote in message
news:336DF028-34FB-431C-8739-C35C9FBF7460@.microsoft.com...
> when i try that it says,
> Cannot change the owner of the master database.
> "Tibor Karaszi" wrote:
>> sp_changedbowner
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Jagminder Chugh" <JagminderChugh@.discussions.microsoft.com> wrote in message
>> news:3250D5AC-F964-4BD7-8C09-C0773AD46721@.microsoft.com...
>> > Hi
>> > In one of the databases, dbo users under Users option does not have the
>> > login name.Is there any way to assign a login name "sa" to dbo.
>> >
>> > Thanks and Regards
>> > Jagminder
>>|||hey tibor, your site is down..|||Thanks. It was down for a while, but I can reach it now...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mb" <mb@.discussions.microsoft.com> wrote in message
news:CE44A7CB-CD22-41D7-991D-91817BE5D28F@.microsoft.com...
> hey tibor, your site is down..
>|||Usually that happens when you restore databases from different servers.
Anyway, try running sp_change_users_login. You can find it in the BOL.
--
Lionel Chacon
"Jagminder Chugh" wrote:
> Hi
> In one of the databases, dbo users under Users option does not have the
> login name.Is there any way to assign a login name "sa" to dbo.
> Thanks and Regards
> Jagminder|||sp_change_users_login doesn't work for the owner of the database. For that you need to use
sp_changedbowner.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lionel Chacon" <LionelChacon@.discussions.microsoft.com> wrote in message
news:5C767497-923B-4041-9A25-787C6D5A4801@.microsoft.com...
> Usually that happens when you restore databases from different servers.
> Anyway, try running sp_change_users_login. You can find it in the BOL.
> --
> Lionel Chacon
>
> "Jagminder Chugh" wrote:
>> Hi
>> In one of the databases, dbo users under Users option does not have the
>> login name.Is there any way to assign a login name "sa" to dbo.
>> Thanks and Regards
>> Jagminder

dbo does not have a login name..

Hi
In one of the databases, dbo users under Users option does not have the
login name.Is there any way to assign a login name "sa" to dbo.
Thanks and Regards
Jagmindersp_changedbowner
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jagminder Chugh" <JagminderChugh@.discussions.microsoft.com> wrote in messag
e
news:3250D5AC-F964-4BD7-8C09-C0773AD46721@.microsoft.com...
> Hi
> In one of the databases, dbo users under Users option does not have the
> login name.Is there any way to assign a login name "sa" to dbo.
> Thanks and Regards
> Jagminder|||when i try that it says,
Cannot change the owner of the master database.
"Tibor Karaszi" wrote:

> sp_changedbowner
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jagminder Chugh" <JagminderChugh@.discussions.microsoft.com> wrote in mess
age
> news:3250D5AC-F964-4BD7-8C09-C0773AD46721@.microsoft.com...
>
>|||Are you saying that it is the *master* database that doesn't have an owner?
That shouldn't be
possible. The proc changes the owner for the current database, so you need t
o USE the database you
want to change owner for first. See Books Online for details on how to use t
his procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jagminder Chugh" <JagminderChugh@.discussions.microsoft.com> wrote in messag
e
news:336DF028-34FB-431C-8739-C35C9FBF7460@.microsoft.com...[vbcol=seagreen]
> when i try that it says,
> Cannot change the owner of the master database.
> "Tibor Karaszi" wrote:
>|||hey tibor, your site is down..|||Thanks. It was down for a while, but I can reach it now...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mb" <mb@.discussions.microsoft.com> wrote in message
news:CE44A7CB-CD22-41D7-991D-91817BE5D28F@.microsoft.com...
> hey tibor, your site is down..
>|||Usually that happens when you restore databases from different servers.
Anyway, try running sp_change_users_login. You can find it in the BOL.
--
Lionel Chacon
"Jagminder Chugh" wrote:

> Hi
> In one of the databases, dbo users under Users option does not have the
> login name.Is there any way to assign a login name "sa" to dbo.
> Thanks and Regards
> Jagminder|||sp_change_users_login doesn't work for the owner of the database. For that y
ou need to use
sp_changedbowner.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lionel Chacon" <LionelChacon@.discussions.microsoft.com> wrote in message
news:5C767497-923B-4041-9A25-787C6D5A4801@.microsoft.com...[vbcol=seagreen]
> Usually that happens when you restore databases from different servers.
> Anyway, try running sp_change_users_login. You can find it in the BOL.
> --
> Lionel Chacon
>
> "Jagminder Chugh" wrote:
>

dbo > login name, it's not sa

I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
bothers me as I am trying to resolve many security issues. I've used
SP_Changedbowner and the database properties now show the owner as sa, but
when I drill into the user list under the database, the columns displayed
(Name, Login Name, Database Access) show the Name as dbo, but the Login Name
is still NOT sa. <puzzling> Does someone out there have an answer to this
for me? Can you tell me how to reset the DBO to sa, like the other 50
databases on this server?
Thank You!!!
Ok, naivigate to Users (under specific database) and click refresh
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
>bothers me as I am trying to resolve many security issues. I've used
>SP_Changedbowner and the database properties now show the owner as sa, but
>when I drill into the user list under the database, the columns displayed
>(Name, Login Name, Database Access) show the Name as dbo, but the Login
>Name is still NOT sa. <puzzling> Does someone out there have an answer to
>this for me? Can you tell me how to reset the DBO to sa, like the other
>50 databases on this server?
> Thank You!!!
>
|||Since you used TSQL code to change the owner, can you try looking at the
owner name using TSQL?
This code will give you the name of all your databases, and login names of
the database owners:
select name, suser_sname(sid)
from master..sysdatabases
If the code shows you the correct owner name, you are good to go.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
>bothers me as I am trying to resolve many security issues. I've used
>SP_Changedbowner and the database properties now show the owner as sa, but
>when I drill into the user list under the database, the columns displayed
>(Name, Login Name, Database Access) show the Name as dbo, but the Login
>Name is still NOT sa. <puzzling> Does someone out there have an answer to
>this for me? Can you tell me how to reset the DBO to sa, like the other
>50 databases on this server?
> Thank You!!!
>
|||Thank you, I did find that refreshing EM displayed the current owner, but
it's also nice to know the TSQL command for this... :-)
==================
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OkLR45zDHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Since you used TSQL code to change the owner, can you try looking at the
> owner name using TSQL?
> This code will give you the name of all your databases, and login names of
> the database owners:
> select name, suser_sname(sid)
> from master..sysdatabases
> If the code shows you the correct owner name, you are good to go.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>

dbo > login name, it's not sa

I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
bothers me as I am trying to resolve many security issues. I've used
SP_Changedbowner and the database properties now show the owner as sa, but
when I drill into the user list under the database, the columns displayed
(Name, Login Name, Database Access) show the Name as dbo, but the Login Name
is still NOT sa. <puzzling> Does someone out there have an answer to this
for me? Can you tell me how to reset the DBO to sa, like the other 50
databases on this server'
Thank You!!!Ok, naivigate to Users (under specific database) and click refresh
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
>bothers me as I am trying to resolve many security issues. I've used
>SP_Changedbowner and the database properties now show the owner as sa, but
>when I drill into the user list under the database, the columns displayed
>(Name, Login Name, Database Access) show the Name as dbo, but the Login
>Name is still NOT sa. <puzzling> Does someone out there have an answer to
>this for me? Can you tell me how to reset the DBO to sa, like the other
>50 databases on this server'
> Thank You!!!
>|||Since you used TSQL code to change the owner, can you try looking at the
owner name using TSQL?
This code will give you the name of all your databases, and login names of
the database owners:
select name, suser_sname(sid)
from master..sysdatabases
If the code shows you the correct owner name, you are good to go.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
>bothers me as I am trying to resolve many security issues. I've used
>SP_Changedbowner and the database properties now show the owner as sa, but
>when I drill into the user list under the database, the columns displayed
>(Name, Login Name, Database Access) show the Name as dbo, but the Login
>Name is still NOT sa. <puzzling> Does someone out there have an answer to
>this for me? Can you tell me how to reset the DBO to sa, like the other
>50 databases on this server'
> Thank You!!!
>|||Thank you, I did find that refreshing EM displayed the current owner, but
it's also nice to know the TSQL command for this... :-)
=================="Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OkLR45zDHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Since you used TSQL code to change the owner, can you try looking at the
> owner name using TSQL?
> This code will give you the name of all your databases, and login names of
> the database owners:
> select name, suser_sname(sid)
> from master..sysdatabases
> If the code shows you the correct owner name, you are good to go.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>>I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
>>bothers me as I am trying to resolve many security issues. I've used
>>SP_Changedbowner and the database properties now show the owner as sa, but
>>when I drill into the user list under the database, the columns displayed
>>(Name, Login Name, Database Access) show the Name as dbo, but the Login
>>Name is still NOT sa. <puzzling> Does someone out there have an answer
>>to this for me? Can you tell me how to reset the DBO to sa, like the
>>other 50 databases on this server'
>> Thank You!!!
>

dbo > login name, it's not sa

I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
bothers me as I am trying to resolve many security issues. I've used
SP_Changedbowner and the database properties now show the owner as sa, but
when I drill into the user list under the database, the columns displayed
(Name, Login Name, Database Access) show the Name as dbo, but the Login Name
is still NOT sa. <puzzling> Does someone out there have an answer to this
for me? Can you tell me how to reset the DBO to sa, like the other 50
databases on this server'
Thank You!!!Ok, naivigate to Users (under specific database) and click refresh
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
>bothers me as I am trying to resolve many security issues. I've used
>SP_Changedbowner and the database properties now show the owner as sa, but
>when I drill into the user list under the database, the columns displayed
>(Name, Login Name, Database Access) show the Name as dbo, but the Login
>Name is still NOT sa. <puzzling> Does someone out there have an answer to
>this for me? Can you tell me how to reset the DBO to sa, like the other
>50 databases on this server'
> Thank You!!!
>|||Since you used TSQL code to change the owner, can you try looking at the
owner name using TSQL?
This code will give you the name of all your databases, and login names of
the database owners:
select name, suser_sname(sid)
from master..sysdatabases
If the code shows you the correct owner name, you are good to go.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
>bothers me as I am trying to resolve many security issues. I've used
>SP_Changedbowner and the database properties now show the owner as sa, but
>when I drill into the user list under the database, the columns displayed
>(Name, Login Name, Database Access) show the Name as dbo, but the Login
>Name is still NOT sa. <puzzling> Does someone out there have an answer to
>this for me? Can you tell me how to reset the DBO to sa, like the other
>50 databases on this server'
> Thank You!!!
>|||Thank you, I did find that refreshing EM displayed the current owner, but
it's also nice to know the TSQL command for this... :-)
==================
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OkLR45zDHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Since you used TSQL code to change the owner, can you try looking at the
> owner name using TSQL?
> This code will give you the name of all your databases, and login names of
> the database owners:
> select name, suser_sname(sid)
> from master..sysdatabases
> If the code shows you the correct owner name, you are good to go.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>

DBO

Friday, February 17, 2012

DBO

On our dev server the app developers have been granted dbo access to their
individual databases. They do not have sa rights on the Dev SQL Server.
The problem is that when the app developers create new objects, they are the
owners.
For example, user1.testtable.
Since user1 has dbo access, is there a way that when user1 creates an object
it gets qualified as dbo.testtable?
The problem is that with some of these RAD tools the database objects are
qualified with the owner name in the code. For example user1.table.
But when we rollout the changes in production, since the dba creates the
objects the owner is the dbo and the application stops working. How can i
reoslve this problem without giving sa access to the developer on the dev db
servers?Hi
I presume that when you say 'granted dbo access' you mean that you put the
users in the db_owner role.
Saying 'granted dbo access' is a misnomer, because dbo is a valid user name,
and unless a user has that name, they actually do not have dbo access.
You can check to see what user name someone is using by running
SELECT user_name()
By default, if you have permission to create an object, any objects you
created will be owned by you, and marked with your user name.
If you are in the db_owner role, your user name may be fred, and then your
objects be referenced as fred.someobject.
However, users in the db_owner role do have the right to give a different
owner to objects they create. So fred, in the db_owner role, can create a
table owned by the dbo user:
CREATE dbo.newtable ( column definitions ...)
A SQL Server database allows multiple objects of the same name, because the
uniqueness only needs to be on the ownername/objectname combination,. So the
fact that the RAD tools qualify with the owner name is essential. dbo.table1
and user1.table1 are two completely different objects. The tool has to make
sure it references the correct object.
If you try to access an object without specifying the owner, SQL Server has
to guess who owns the object. It will first guess that you (the current
user) own the object, then it will guess that the dbo owns the object. If
neither the current user or dbo owns the object you are referencing, you
will get an error about an unknown object.
Even though SQL Server can guess the owner correctly in some cases, I
suggest you get in the habit of always specifying the owner name, even if
the dbo owns the object. This habit will put you in good shape if you are
ever planning to upgrade to SQL Server 2005. There are also some performance
benefits in SQL 7/2000 to be gained when not relying on SQL Server to figure
out the owner.
So I'm not sure if the 'problem' you wanted to solve was that objects
created by your db_owner users were not owned by dbo, or that the RAD tools
always would qualify objects with their owners. Hopefully, the above will
help in either case. If not, please ask for elaboration.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"docsql" <docsql@.noemail.nospam> wrote in message
news:%23ingdRK4FHA.1596@.tk2msftngp13.phx.gbl...
> On our dev server the app developers have been granted dbo access to their
> individual databases. They do not have sa rights on the Dev SQL Server.
> The problem is that when the app developers create new objects, they are
> the owners.
> For example, user1.testtable.
> Since user1 has dbo access, is there a way that when user1 creates an
> object it gets qualified as dbo.testtable?
> The problem is that with some of these RAD tools the database objects are
> qualified with the owner name in the code. For example user1.table.
> But when we rollout the changes in production, since the dba creates the
> objects the owner is the dbo and the application stops working. How can i
> reoslve this problem without giving sa access to the developer on the dev
> db servers?
>

DBO

On our dev server the app developers have been granted dbo access to their
individual databases. They do not have sa rights on the Dev SQL Server.
The problem is that when the app developers create new objects, they are the
owners.
For example, user1.testtable.
Since user1 has dbo access, is there a way that when user1 creates an object
it gets qualified as dbo.testtable?
The problem is that with some of these RAD tools the database objects are
qualified with the owner name in the code. For example user1.table.
But when we rollout the changes in production, since the dba creates the
objects the owner is the dbo and the application stops working. How can i
reoslve this problem without giving sa access to the developer on the dev db
servers?Hi
I presume that when you say 'granted dbo access' you mean that you put the
users in the db_owner role.
Saying 'granted dbo access' is a misnomer, because dbo is a valid user name,
and unless a user has that name, they actually do not have dbo access.
You can check to see what user name someone is using by running
SELECT user_name()
By default, if you have permission to create an object, any objects you
created will be owned by you, and marked with your user name.
If you are in the db_owner role, your user name may be fred, and then your
objects be referenced as fred.someobject.
However, users in the db_owner role do have the right to give a different
owner to objects they create. So fred, in the db_owner role, can create a
table owned by the dbo user:
CREATE dbo.newtable ( column definitions ...)
A SQL Server database allows multiple objects of the same name, because the
uniqueness only needs to be on the ownername/objectname combination,. So the
fact that the RAD tools qualify with the owner name is essential. dbo.table1
and user1.table1 are two completely different objects. The tool has to make
sure it references the correct object.
If you try to access an object without specifying the owner, SQL Server has
to guess who owns the object. It will first guess that you (the current
user) own the object, then it will guess that the dbo owns the object. If
neither the current user or dbo owns the object you are referencing, you
will get an error about an unknown object.
Even though SQL Server can guess the owner correctly in some cases, I
suggest you get in the habit of always specifying the owner name, even if
the dbo owns the object. This habit will put you in good shape if you are
ever planning to upgrade to SQL Server 2005. There are also some performance
benefits in SQL 7/2000 to be gained when not relying on SQL Server to figure
out the owner.
So I'm not sure if the 'problem' you wanted to solve was that objects
created by your db_owner users were not owned by dbo, or that the RAD tools
always would qualify objects with their owners. Hopefully, the above will
help in either case. If not, please ask for elaboration.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"docsql" <docsql@.noemail.nospam> wrote in message
news:%23ingdRK4FHA.1596@.tk2msftngp13.phx.gbl...
> On our dev server the app developers have been granted dbo access to their
> individual databases. They do not have sa rights on the Dev SQL Server.
> The problem is that when the app developers create new objects, they are
> the owners.
> For example, user1.testtable.
> Since user1 has dbo access, is there a way that when user1 creates an
> object it gets qualified as dbo.testtable?
> The problem is that with some of these RAD tools the database objects are
> qualified with the owner name in the code. For example user1.table.
> But when we rollout the changes in production, since the dba creates the
> objects the owner is the dbo and the application stops working. How can i
> reoslve this problem without giving sa access to the developer on the dev
> db servers?
>

DBO

On our dev server the app developers have been granted dbo access to their
individual databases. They do not have sa rights on the Dev SQL Server.
The problem is that when the app developers create new objects, they are the
owners.
For example, user1.testtable.
Since user1 has dbo access, is there a way that when user1 creates an object
it gets qualified as dbo.testtable?
The problem is that with some of these RAD tools the database objects are
qualified with the owner name in the code. For example user1.table.
But when we rollout the changes in production, since the dba creates the
objects the owner is the dbo and the application stops working. How can i
reoslve this problem without giving sa access to the developer on the dev db
servers?
Hi
I presume that when you say 'granted dbo access' you mean that you put the
users in the db_owner role.
Saying 'granted dbo access' is a misnomer, because dbo is a valid user name,
and unless a user has that name, they actually do not have dbo access.
You can check to see what user name someone is using by running
SELECT user_name()
By default, if you have permission to create an object, any objects you
created will be owned by you, and marked with your user name.
If you are in the db_owner role, your user name may be fred, and then your
objects be referenced as fred.someobject.
However, users in the db_owner role do have the right to give a different
owner to objects they create. So fred, in the db_owner role, can create a
table owned by the dbo user:
CREATE dbo.newtable ( column definitions ...)
A SQL Server database allows multiple objects of the same name, because the
uniqueness only needs to be on the ownername/objectname combination,. So the
fact that the RAD tools qualify with the owner name is essential. dbo.table1
and user1.table1 are two completely different objects. The tool has to make
sure it references the correct object.
If you try to access an object without specifying the owner, SQL Server has
to guess who owns the object. It will first guess that you (the current
user) own the object, then it will guess that the dbo owns the object. If
neither the current user or dbo owns the object you are referencing, you
will get an error about an unknown object.
Even though SQL Server can guess the owner correctly in some cases, I
suggest you get in the habit of always specifying the owner name, even if
the dbo owns the object. This habit will put you in good shape if you are
ever planning to upgrade to SQL Server 2005. There are also some performance
benefits in SQL 7/2000 to be gained when not relying on SQL Server to figure
out the owner.
So I'm not sure if the 'problem' you wanted to solve was that objects
created by your db_owner users were not owned by dbo, or that the RAD tools
always would qualify objects with their owners. Hopefully, the above will
help in either case. If not, please ask for elaboration.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"docsql" <docsql@.noemail.nospam> wrote in message
news:%23ingdRK4FHA.1596@.tk2msftngp13.phx.gbl...
> On our dev server the app developers have been granted dbo access to their
> individual databases. They do not have sa rights on the Dev SQL Server.
> The problem is that when the app developers create new objects, they are
> the owners.
> For example, user1.testtable.
> Since user1 has dbo access, is there a way that when user1 creates an
> object it gets qualified as dbo.testtable?
> The problem is that with some of these RAD tools the database objects are
> qualified with the owner name in the code. For example user1.table.
> But when we rollout the changes in production, since the dba creates the
> objects the owner is the dbo and the application stops working. How can i
> reoslve this problem without giving sa access to the developer on the dev
> db servers?
>

DBNETLIB general network error

Hello,

We have a problem on one of our databases: sporadically all XP machines remotelly connected to that database -which is the haviest one- all of them are dropped and at the next request the egt an error:"[DBNETLIB]general network error", it does not happen to win 98 machines or to clients connecting to other databases on the same Mssql server. I have a feeling that it is Mssql connectivity installed that is cauing it but I do not see any option to uninstall it.
Please help!!!Im finding lots of problems on any SQL machines running MDAC 2.8

As far as I can see the whole world is screaming...

Dont wish to scare you... but check your MDAC version... if its 2.8 I would say expect problems... and if you find any solutions... please let me know :)

Im still searching for myself... I will of course post anything I find

LFN|||where do I check the version?
Originally posted by LFN
Im finding lots of problems on any SQL machines running MDAC 2.8

As far as I can see the whole world is screaming...

Dont wish to scare you... but check your MDAC version... if its 2.8 I would say expect problems... and if you find any solutions... please let me know :)

Im still searching for myself... I will of course post anything I find

LFN|||ok - most importantly understand i am not an expert on this issue. Check everything :)

I have transferred my application back to an sql 7.0 server and everything is working 100%... also works 100% on SQL 2000 with mdac 2.5

... im not sure how to get mdac version... there is a SELECT @.@.version command in sql - but i dont know if it returns mdac...

Im using asp and I simply did a conn.version statement... and as its the connection that seems to be causing the problems I reccommend you get the properties from the connection. Im not sure if the iis or the sql server mdac is returned... tho some say different mdac versions on the two can cause problems.

I cant be 100% that this is your problem... but everywhere i look im finding people having trouble with sql 2k sp3 mdac 2.8|||In the registry, under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DataAccess,
there are string values called FullInstallVer and Version. One or both contain the MDAC version number; not sure what the difference is. My machine has the same value for both (2.80.1022.3).|||Rather accessing the registry if you're not aware of that, better to get COMCHECK tool from MS which will return complete information if any discrepancies listed.

Ensure similar netlib protocols are enabled between the clients and server.

Saturday, February 25, 2012

DBs not appearing in query analyer dropdown

DBREINDEX at threshold for all databases

Friday, February 24, 2012

dbowner no more dbower (SID lost)

dbo with user name in "users"

DBO user missing in two databases

Sunday, February 19, 2012

dbo question

dbo question

dbo question

dbo login name wrong after copy

dbo does not have a login name..

dbo does not have a login name..

dbo does not have a login name..

dbo > login name, it's not sa

dbo > login name, it's not sa

dbo > login name, it's not sa

DBO

Friday, February 17, 2012

DBO

DBO

DBO

DBNETLIB general network error

deAttach database

Blog Archive

About Me