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