default user for object, sysadmin role, KB22067, invalid object

Trying to select rows from a table with a particular owner
which has unfortunately been set to the sysadmin role for
sqlserver by some predecessor.
'select * from mytable' gives error message 'invalid object'
This is an inconvenience
because we have that owner name hardcoded in an unknown
number of places in the code, and many other places queries are
coded in without any owner reference.
In reference to KB255067 it gives two unrealistic workarounds
* don't set the owner in that role
(Waiting for Microsoft TimeMachine(TM) to come out)
* create all the tables with DBO owner
My colleague has invented a workaround where we set the
sysusers.sid to the user we want it to be instead of to 0x01
but I am unsure of the consequences to that.
I would like to know what a truly useable workaround is
that will not have weird consequences later on so that
SqlServer will do what I want it to do.
For example a real workaround would be
* Mark up that user so the evidence of it having been in
the sysadmin role is removed
or in the wording of the KB article
* reconstruct or fix the ownership chain (since it was broken
when the sysadmin role was used)
DO NOT MODIFY SYSTEM TABLES DIRECTLY! Yes, a "qualified" dba could "get
away" with modifying system tables, but merely changing the owner sid is
insufficient as it will not modify the sysdepends table references.
Use sp_changeobjectowner and modify the owner of the object to dbo. If the
old owner was qualified and hard-coded, then map the application login, or
another login, to an aliased user to that old owner name.
A final consideration would be to script out all of those objects, export
the data, drop the objects, and then recreate the objects under a legitimate
database user (note, not login). Then, reimport the data.
Sincerely,
Anthony Thomas

"ElvinKee" <ElvinKee@.discussions.microsoft.com> wrote in message
news:D09D8C22-AD0D-4DBD-81DC-42EACF0CC07E@.microsoft.com...
> Trying to select rows from a table with a particular owner
> which has unfortunately been set to the sysadmin role for
> sqlserver by some predecessor.
> 'select * from mytable' gives error message 'invalid object'
> This is an inconvenience
> because we have that owner name hardcoded in an unknown
> number of places in the code, and many other places queries are
> coded in without any owner reference.
> In reference to KB255067 it gives two unrealistic workarounds
> * don't set the owner in that role
> (Waiting for Microsoft TimeMachine(TM) to come out)
> * create all the tables with DBO owner
> My colleague has invented a workaround where we set the
> sysusers.sid to the user we want it to be instead of to 0x01
> but I am unsure of the consequences to that.
> I would like to know what a truly useable workaround is
> that will not have weird consequences later on so that
> SqlServer will do what I want it to do.
> For example a real workaround would be
> * Mark up that user so the evidence of it having been in
> the sysadmin role is removed
> or in the wording of the KB article
> * reconstruct or fix the ownership chain (since it was broken
> when the sysadmin role was used)
>
>
> --

default user for object, sysadmin role, KB22067, invalid object

Trying to select rows from a table with a particular owner
which has unfortunately been set to the sysadmin role for
sqlserver by some predecessor.
'select * from mytable' gives error message 'invalid object'
This is an inconvenience
because we have that owner name hardcoded in an unknown
number of places in the code, and many other places queries are
coded in without any owner reference.
In reference to KB255067 it gives two unrealistic workarounds
* don't set the owner in that role
(Waiting for Microsoft TimeMachine(TM) to come out)
* create all the tables with DBO owner
My colleague has invented a workaround where we set the
sysusers.sid to the user we want it to be instead of to 0x01
but I am unsure of the consequences to that.
I would like to know what a truly useable workaround is
that will not have weird consequences later on so that
SqlServer will do what I want it to do.
For example a real workaround would be
* Mark up that user so the evidence of it having been in
the sysadmin role is removed
or in the wording of the KB article
* reconstruct or fix the ownership chain (since it was broken
when the sysadmin role was used)DO NOT MODIFY SYSTEM TABLES DIRECTLY! Yes, a "qualified" dba could "get
away" with modifying system tables, but merely changing the owner sid is
insufficient as it will not modify the sysdepends table references.
Use sp_changeobjectowner and modify the owner of the object to dbo. If the
old owner was qualified and hard-coded, then map the application login, or
another login, to an aliased user to that old owner name.
A final consideration would be to script out all of those objects, export
the data, drop the objects, and then recreate the objects under a legitimate
database user (note, not login). Then, reimport the data.
Sincerely,
Anthony Thomas
"ElvinKee" <ElvinKee@.discussions.microsoft.com> wrote in message
news:D09D8C22-AD0D-4DBD-81DC-42EACF0CC07E@.microsoft.com...
> Trying to select rows from a table with a particular owner
> which has unfortunately been set to the sysadmin role for
> sqlserver by some predecessor.
> 'select * from mytable' gives error message 'invalid object'
> This is an inconvenience
> because we have that owner name hardcoded in an unknown
> number of places in the code, and many other places queries are
> coded in without any owner reference.
> In reference to KB255067 it gives two unrealistic workarounds
> * don't set the owner in that role
> (Waiting for Microsoft TimeMachine(TM) to come out)
> * create all the tables with DBO owner
> My colleague has invented a workaround where we set the
> sysusers.sid to the user we want it to be instead of to 0x01
> but I am unsure of the consequences to that.
> I would like to know what a truly useable workaround is
> that will not have weird consequences later on so that
> SqlServer will do what I want it to do.
> For example a real workaround would be
> * Mark up that user so the evidence of it having been in
> the sysadmin role is removed
> or in the wording of the KB article
> * reconstruct or fix the ownership chain (since it was broken
> when the sysadmin role was used)
>
>
> --

Default Schema on SQL Server 2005 with Server Role "sysadmin"

Hi,
I have a user with a default database and a default schema that is not
"dbo". I noticed that, if I assign this user the server role "sysadmin"
the default schema does not seem to be honored any more. Instead
whenever I use an unqualified table name "dbo" is searched first. When
I remove the role, the user's default schema is searched as first
schema. Is there any way to make SQL 2005 honor the default schema when
assigning role "sysadmin" to a user? I could not find anything in the
docs about this.
Kind regards
robertRobert,
Sysadmins always map to dbo in a database. Being a sysadmin trumps any
other settings for the user.
RLF
"Robert Klemme" <shortcutter@.googlemail.com> wrote in message
news:55svpoF24qv1vU1@.mid.individual.net...
> Hi,
> I have a user with a default database and a default schema that is not
> "dbo". I noticed that, if I assign this user the server role "sysadmin"
> the default schema does not seem to be honored any more. Instead whenever
> I use an unqualified table name "dbo" is searched first. When I remove
> the role, the user's default schema is searched as first schema. Is there
> any way to make SQL 2005 honor the default schema when assigning role
> "sysadmin" to a user? I could not find anything in the docs about this.
> Kind regards
> robert|||On 15.03.2007 14:53, Russell Fields wrote:
> Sysadmins always map to dbo in a database. Being a sysadmin trumps any
> other settings for the user.
Ah! Thanks a lot!
Kind regards
robert

Default Schema on SQL Server 2005 with Server Role "sysadmin"

Hi,
I have a user with a default database and a default schema that is not
"dbo". I noticed that, if I assign this user the server role "sysadmin"
the default schema does not seem to be honored any more. Instead
whenever I use an unqualified table name "dbo" is searched first. When
I remove the role, the user's default schema is searched as first
schema. Is there any way to make SQL 2005 honor the default schema when
assigning role "sysadmin" to a user? I could not find anything in the
docs about this.
Kind regards
robertRobert,
Sysadmins always map to dbo in a database. Being a sysadmin trumps any
other settings for the user.
RLF
"Robert Klemme" <shortcutter@.googlemail.com> wrote in message
news:55svpoF24qv1vU1@.mid.individual.net...
> Hi,
> I have a user with a default database and a default schema that is not
> "dbo". I noticed that, if I assign this user the server role "sysadmin"
> the default schema does not seem to be honored any more. Instead whenever
> I use an unqualified table name "dbo" is searched first. When I remove
> the role, the user's default schema is searched as first schema. Is there
> any way to make SQL 2005 honor the default schema when assigning role
> "sysadmin" to a user? I could not find anything in the docs about this.
> Kind regards
> robert|||On 15.03.2007 14:53, Russell Fields wrote:
> Sysadmins always map to dbo in a database. Being a sysadmin trumps any
> other settings for the user.
Ah! Thanks a lot!
Kind regards
robert

default schema (SQL Server 2005)

Is there anyway to change the default schema for a user who is a
member of "sysadmin". It appears that Microsoft has made the default
schema for this role "dbo" and our application has been writtem not to
include owner/schema name.A sysadmin works within databases in the dbo context, so it will use the dbo
schema (which has always been the default schema for sysadmins, except that
is was called 'owner' in SQL ).
So, I don't think you have an option on this.
RLF
<dnlgauthier@.hotmail.com> wrote in message
news:1187272016.929096.136070@.22g2000hsm.googlegroups.com...
> Is there anyway to change the default schema for a user who is a
> member of "sysadmin". It appears that Microsoft has made the default
> schema for this role "dbo" and our application has been writtem not to
> include owner/schema name.
>|||And I did try changing the dbo user's default schema to something else, but
SQL Server will prevent that with:
Msg 15150, Level 16, State 1, Line 1
Cannot alter the user 'dbo'.
(And, if it did work, it would be a bad idea.)
RLF
<dnlgauthier@.hotmail.com> wrote in message
news:1187272016.929096.136070@.22g2000hsm.googlegroups.com...
> Is there anyway to change the default schema for a user who is a
> member of "sysadmin". It appears that Microsoft has made the default
> schema for this role "dbo" and our application has been writtem not to
> include owner/schema name.
>|||On Aug 16, 1:26 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> A sysadmin works within databases in the dbo context, so it will use the d
bo
> schema (which has always been the default schema for sysadmins, except tha
t
> is was called 'owner' in SQL ).
> So, I don't think you have an option on this.
> RLF<dnlgauth...@.hotmail.com> wrote in message
> news:1187272016.929096.136070@.22g2000hsm.googlegroups.com...
>
>
> - Show quoted text -
I agree with the statement that a sysadmin works with in teh database
as "dbo" context, so how can I provide user access via an application
using Windows authenticate and make it so that he/she is a user not a
member of sysadmin? I hope this is not confuising ?|||dnl,
So the answer to your first question was "sqladmin" will always be "dbo" in
a database.
The answer to your second question comes in two parts.
1 - General users should NEVER be sysadmins.
2 - Grant them specific rights either as their personal login or by granting
rights to a Windows Group in which they are a member. (I prefer assigning
rights to Windows groups over individual logins.)
E.g.
1. MikeR needs rights to MyDatabase.
2. MikeR is a member of the Windows group Domain\MyDatabaseUsers
3. Add Domain\MyDatabaseUsers as a login on your SQL Server.
4. In MyDatabase create a security role named GeneralUsers
5. Add Domain\MyDatabaseUsers as a user of MyDatabase and a member of
GeneralUsers.
6. All objects in the database that GeneralUsers should need need to have
rights granted to that role.
a. GRANT SELECT ON ViewA TO GeneralUsers, etc.
b. GRANT EXECUTE ON UpdateSomethingProc TO GeneralUsers, etc.
In the future, when MikeR moves on, simple remove him from the Windows
group. When LisaK takes his place, add her to the Windows group. And so on
and so on. Here is a whitepaper on SQL Server 2005 Security Best Practices.
http://download.microsoft.com/downl...c#_Toc160646532
RLF
<dnlgauthier@.hotmail.com> wrote in message
news:1187292090.432045.126570@.19g2000hsx.googlegroups.com...
> On Aug 16, 1:26 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> I agree with the statement that a sysadmin works with in teh database
> as "dbo" context, so how can I provide user access via an application
> using Windows authenticate and make it so that he/she is a user not a
> member of sysadmin? I hope this is not confuising ?
>|||(dnlgauthier@.hotmail.com) writes:
> I agree with the statement that a sysadmin works with in teh database
> as "dbo" context, so how can I provide user access via an application
> using Windows authenticate and make it so that he/she is a user not a
> member of sysadmin? I hope this is not confuising ?
Use EXECUTE AS to impersonate a different login or user.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx