User table updates are recorded as dbo instead of userid

User table updates are recorded as dbo instead of userid

I am working on a vb.net app that connects to a SQL Server 2005 database.  
The app uses "Trusted Connection" to connect to the db and no userid/password 
is passed.  I am a member of a security group called "Developers".  Up until 
last week, when I updated a row in a database table from the app, an Audit 
table recorded the "Performed By" userid as my Windows (Active Directory) 
userid.  After I was unable to step into a SQL Stored Procedure last week, my 
manager tried modifying permissions to allow me to debug the proc.  We 
finally got that to work, but now whenever anyone in the Developer group 
updates a table, the update is recorded as being done by "dbo" instead of the 
userid.  My manager un-did whatever he did (he says, but I wonder) last week, 
but we still have the problem.  I have now inherited the task of getting us 
back on track, but am not sure where to start.  I tried changing the owner of 
our test database from sa to myself, but still other members of Developers 
are showing up as "dbo".  I read about Aliases and wonder if maybe that's 
what we have, but am not sure how to find out.  Any help would be greatly 
appreciated.

Thanks.

date: Tue, 26 Aug 2008 12:19:03 -0700 author: ginacresse

Re: User table updates are recorded as dbo instead of userid

> but now whenever anyone in the Developer group
> updates a table, the update is recorded as being done by "dbo" instead of 
> the
> userid.

As you may know, the only logins mapped to the dbo user are 1) the database 
owner, 2) sysadmin role members and 3) dbo aliases.

I suggest you double-check sysadmin role membership.  xp_logininfo will list 
all permission paths (including Windows groups) that provide access from the 
specified account and indicate if the user/group is a sysadmin:

EXEC master..xp_logininfo 'MyDomain\MyIndividualAccount', 'all'

> I read about Aliases and wonder if maybe that's
> what we have, but am not sure how to find out.  Any help would be greatly
> appreciated.

You can list dbo aliases with sp_helpuser and drop with sp_dropalias.  Note 
that aliases were deprecated several years ago:

EXEC sp_helpuser 'dbo'
EXEC sp_dropalias 'some_login'

-- 
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"ginacresse"  wrote in message 
news:9E3F3079-C6F6-49DD-B933-929DDFAFA946@microsoft.com...
>I am working on a vb.net app that connects to a SQL Server 2005 database.
> The app uses "Trusted Connection" to connect to the db and no 
> userid/password
> is passed.  I am a member of a security group called "Developers".  Up 
> until
> last week, when I updated a row in a database table from the app, an Audit
> table recorded the "Performed By" userid as my Windows (Active Directory)
> userid.  After I was unable to step into a SQL Stored Procedure last week, 
> my
> manager tried modifying permissions to allow me to debug the proc.  We
> finally got that to work, but now whenever anyone in the Developer group
> updates a table, the update is recorded as being done by "dbo" instead of 
> the
> userid.  My manager un-did whatever he did (he says, but I wonder) last 
> week,
> but we still have the problem.  I have now inherited the task of getting 
> us
> back on track, but am not sure where to start.  I tried changing the owner 
> of
> our test database from sa to myself, but still other members of Developers
> are showing up as "dbo".  I read about Aliases and wonder if maybe that's
> what we have, but am not sure how to find out.  Any help would be greatly
> appreciated.
>
> Thanks.

date: Wed, 27 Aug 2008 07:19:54 -0500 author: Dan Guzman

Re: User table updates are recorded as dbo instead of userid

Thanks for the reply, Dan.  From what I can tell, all Developers are also 
members of BUILTIN\Administrators.  Would this be the equivelent of all 
Developers having sysadmin permissions?  And would that cause us all to show 
up as dbo when updating tables?

Gina

"Dan Guzman" wrote:

> > but now whenever anyone in the Developer group
> > updates a table, the update is recorded as being done by "dbo" instead of 
> > the
> > userid.
> 
> As you may know, the only logins mapped to the dbo user are 1) the database 
> owner, 2) sysadmin role members and 3) dbo aliases.
> 
> I suggest you double-check sysadmin role membership.  xp_logininfo will list 
> all permission paths (including Windows groups) that provide access from the 
> specified account and indicate if the user/group is a sysadmin:
> 
> EXEC master..xp_logininfo 'MyDomain\MyIndividualAccount', 'all'
> 
> > I read about Aliases and wonder if maybe that's
> > what we have, but am not sure how to find out.  Any help would be greatly
> > appreciated.
> 
> You can list dbo aliases with sp_helpuser and drop with sp_dropalias.  Note 
> that aliases were deprecated several years ago:
> 
> EXEC sp_helpuser 'dbo'
> EXEC sp_dropalias 'some_login'
> 
> -- 
> Hope this helps.
> 
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> 
> "ginacresse"  wrote in message 
> news:9E3F3079-C6F6-49DD-B933-929DDFAFA946@microsoft.com...
> >I am working on a vb.net app that connects to a SQL Server 2005 database.
> > The app uses "Trusted Connection" to connect to the db and no 
> > userid/password
> > is passed.  I am a member of a security group called "Developers".  Up 
> > until
> > last week, when I updated a row in a database table from the app, an Audit
> > table recorded the "Performed By" userid as my Windows (Active Directory)
> > userid.  After I was unable to step into a SQL Stored Procedure last week, 
> > my
> > manager tried modifying permissions to allow me to debug the proc.  We
> > finally got that to work, but now whenever anyone in the Developer group
> > updates a table, the update is recorded as being done by "dbo" instead of 
> > the
> > userid.  My manager un-did whatever he did (he says, but I wonder) last 
> > week,
> > but we still have the problem.  I have now inherited the task of getting 
> > us
> > back on track, but am not sure where to start.  I tried changing the owner 
> > of
> > our test database from sa to myself, but still other members of Developers
> > are showing up as "dbo".  I read about Aliases and wonder if maybe that's
> > what we have, but am not sure how to find out.  Any help would be greatly
> > appreciated.
> >
> > Thanks. 
> 
>

date: Wed, 27 Aug 2008 13:32:08 -0700 author: ginacresse

Re: User table updates are recorded as dbo instead of userid

Yes. If all developers are members of BUILTIN\Administrators, and 
BUILTIN\Administrators is a member of the sysadmin group, then all 
developers will be identified as dbo in every database.
Remove the developers from the local computer Administrators group, and they 
should go back to their old logins, (if you haven't deleted them). Be 
careful not to kick everyone out of the Administrators group and thereby 
lock out all administrators of SQL Server!
-- 
Rick Byham (MSFT), SQL Server Books Online
This posting is provided "AS IS" with no warranties, and confers no rights.

"ginacresse"  wrote in message 
news:D80647B0-8AD6-42D8-B66D-03FA0D9BD3F9@microsoft.com...
> Thanks for the reply, Dan.  From what I can tell, all Developers are also
> members of BUILTIN\Administrators.  Would this be the equivelent of all
> Developers having sysadmin permissions?  And would that cause us all to 
> show
> up as dbo when updating tables?
>
> Gina
>
> "Dan Guzman" wrote:
>
>> > but now whenever anyone in the Developer group
>> > updates a table, the update is recorded as being done by "dbo" instead 
>> > of
>> > the
>> > userid.
>>
>> As you may know, the only logins mapped to the dbo user are 1) the 
>> database
>> owner, 2) sysadmin role members and 3) dbo aliases.
>>
>> I suggest you double-check sysadmin role membership.  xp_logininfo will 
>> list
>> all permission paths (including Windows groups) that provide access from 
>> the
>> specified account and indicate if the user/group is a sysadmin:
>>
>> EXEC master..xp_logininfo 'MyDomain\MyIndividualAccount', 'all'
>>
>> > I read about Aliases and wonder if maybe that's
>> > what we have, but am not sure how to find out.  Any help would be 
>> > greatly
>> > appreciated.
>>
>> You can list dbo aliases with sp_helpuser and drop with sp_dropalias. 
>> Note
>> that aliases were deprecated several years ago:
>>
>> EXEC sp_helpuser 'dbo'
>> EXEC sp_dropalias 'some_login'
>>
>> -- 
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>>
>> "ginacresse"  wrote in message
>> news:9E3F3079-C6F6-49DD-B933-929DDFAFA946@microsoft.com...
>> >I am working on a vb.net app that connects to a SQL Server 2005 
>> >database.
>> > The app uses "Trusted Connection" to connect to the db and no
>> > userid/password
>> > is passed.  I am a member of a security group called "Developers".  Up
>> > until
>> > last week, when I updated a row in a database table from the app, an 
>> > Audit
>> > table recorded the "Performed By" userid as my Windows (Active 
>> > Directory)
>> > userid.  After I was unable to step into a SQL Stored Procedure last 
>> > week,
>> > my
>> > manager tried modifying permissions to allow me to debug the proc.  We
>> > finally got that to work, but now whenever anyone in the Developer 
>> > group
>> > updates a table, the update is recorded as being done by "dbo" instead 
>> > of
>> > the
>> > userid.  My manager un-did whatever he did (he says, but I wonder) last
>> > week,
>> > but we still have the problem.  I have now inherited the task of 
>> > getting
>> > us
>> > back on track, but am not sure where to start.  I tried changing the 
>> > owner
>> > of
>> > our test database from sa to myself, but still other members of 
>> > Developers
>> > are showing up as "dbo".  I read about Aliases and wonder if maybe 
>> > that's
>> > what we have, but am not sure how to find out.  Any help would be 
>> > greatly
>> > appreciated.
>> >
>> > Thanks.
>>
>>

date: Wed, 27 Aug 2008 15:31:56 -0700 author: Rick Byham, \(MSFT\)

Re: User table updates are recorded as dbo instead of userid

Thanks, Rick.  That was my problem.  All is back to normal now.

"Rick Byham, (MSFT)" wrote:

> Yes. If all developers are members of BUILTIN\Administrators, and 
> BUILTIN\Administrators is a member of the sysadmin group, then all 
> developers will be identified as dbo in every database.
> Remove the developers from the local computer Administrators group, and they 
> should go back to their old logins, (if you haven't deleted them). Be 
> careful not to kick everyone out of the Administrators group and thereby 
> lock out all administrators of SQL Server!
> -- 
> Rick Byham (MSFT), SQL Server Books Online
> This posting is provided "AS IS" with no warranties, and confers no rights.
> 
> "ginacresse"  wrote in message 
> news:D80647B0-8AD6-42D8-B66D-03FA0D9BD3F9@microsoft.com...
> > Thanks for the reply, Dan.  From what I can tell, all Developers are also
> > members of BUILTIN\Administrators.  Would this be the equivelent of all
> > Developers having sysadmin permissions?  And would that cause us all to 
> > show
> > up as dbo when updating tables?
> >
> > Gina
> >
> > "Dan Guzman" wrote:
> >
> >> > but now whenever anyone in the Developer group
> >> > updates a table, the update is recorded as being done by "dbo" instead 
> >> > of
> >> > the
> >> > userid.
> >>
> >> As you may know, the only logins mapped to the dbo user are 1) the 
> >> database
> >> owner, 2) sysadmin role members and 3) dbo aliases.
> >>
> >> I suggest you double-check sysadmin role membership.  xp_logininfo will 
> >> list
> >> all permission paths (including Windows groups) that provide access from 
> >> the
> >> specified account and indicate if the user/group is a sysadmin:
> >>
> >> EXEC master..xp_logininfo 'MyDomain\MyIndividualAccount', 'all'
> >>
> >> > I read about Aliases and wonder if maybe that's
> >> > what we have, but am not sure how to find out.  Any help would be 
> >> > greatly
> >> > appreciated.
> >>
> >> You can list dbo aliases with sp_helpuser and drop with sp_dropalias. 
> >> Note
> >> that aliases were deprecated several years ago:
> >>
> >> EXEC sp_helpuser 'dbo'
> >> EXEC sp_dropalias 'some_login'
> >>
> >> -- 
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >> http://weblogs.sqlteam.com/dang/
> >>
> >> "ginacresse"  wrote in message
> >> news:9E3F3079-C6F6-49DD-B933-929DDFAFA946@microsoft.com...
> >> >I am working on a vb.net app that connects to a SQL Server 2005 
> >> >database.
> >> > The app uses "Trusted Connection" to connect to the db and no
> >> > userid/password
> >> > is passed.  I am a member of a security group called "Developers".  Up
> >> > until
> >> > last week, when I updated a row in a database table from the app, an 
> >> > Audit
> >> > table recorded the "Performed By" userid as my Windows (Active 
> >> > Directory)
> >> > userid.  After I was unable to step into a SQL Stored Procedure last 
> >> > week,
> >> > my
> >> > manager tried modifying permissions to allow me to debug the proc.  We
> >> > finally got that to work, but now whenever anyone in the Developer 
> >> > group
> >> > updates a table, the update is recorded as being done by "dbo" instead 
> >> > of
> >> > the
> >> > userid.  My manager un-did whatever he did (he says, but I wonder) last
> >> > week,
> >> > but we still have the problem.  I have now inherited the task of 
> >> > getting
> >> > us
> >> > back on track, but am not sure where to start.  I tried changing the 
> >> > owner
> >> > of
> >> > our test database from sa to myself, but still other members of 
> >> > Developers
> >> > are showing up as "dbo".  I read about Aliases and wonder if maybe 
> >> > that's
> >> > what we have, but am not sure how to find out.  Any help would be 
> >> > greatly
> >> > appreciated.
> >> >
> >> > Thanks.
> >>
> >> 
>

date: Wed, 27 Aug 2008 16:24:14 -0700 author: ginacresse