Re: Alter User for domain user

Any member of sysadmin will always be the dbo inside a DB. Have you tried
creating a separate login that is not a member - directly or indirectly - of
the sysadmin role, and assigning it a default schema?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"David Walker" <none@xxxxxxxx> wrote in message
news:Xns9A97677D55B76DavidWalker@xxxxxxxxxxxxxxxx
Erland Sommarskog <esquel@xxxxxxxxxxxxx> wrote in
news:Xns9A97520A95F6Yazorman@xxxxxxxxx:

David Walker (none@xxxxxxxx) writes:
I still would like for my username to have a default schema. When I
connect to the server with Windows Authentication, the default schema
is not mapped. (I used to have the dbo role, but that's not the case
any more. I know that a user with the dbo role doesn't have a
default schema.)

All users have a default schema, including dbo. But you can't change
the default schema for dbo to anything else than dbo.


Yes, I know that all users have a default schema; getting a non-dbo
default schema is what I am trying to accomplish. I am NOT the dbo, but
my default schema remains at dbo, reegardless of what I do.

I want my default schema to be what I have set it to with "alter user"
just as in your example below.

I see that the IS group, which I am a member of, has the sysadmin fixed
role on the server. Maybe that's the problem.

My specific userid has the "public" role on the server, and additional
roles on the database where I am trying to change my default schema away
from dbo to something else.

Is the my group membership the source of the problem? Again, I don't
see that it's documented that a member of the sysadmin fixed role can't
have a non-dbo default schema. (Not the dbo role, but sysadmin.)


David Walker


I'm not really sure what your problem may be, as I don't know all your
steps. But I did a test, and I was indeed able to set up a default
schema different from dbo for a Windows login. Here is my test script:

create login [YAZORMAN\Laban] from windows
create user [YAZORMAN\Laban]
go
alter user [YAZORMAN\Laban] with DEFAULT_SCHEMA = thatschema
go
create table hyfs(a int NOT NULL)
create table thatschema.hyfs (b int NOT NULL)
GRANT SELECT ON SCHEMA::thatschema TO [YAZORMAN\Laban]
go
EXECUTE AS LOGIN = 'YAZORMAN\Laban'
go
select * from hyfs -- Lists a result set with a column b
go
REVERT





.

Relevant Pages

  • RE: SQL2KSP4 sp_addalias and db_accessadmin
    ... Based on my test, after installing SP4, only member of sysadmin or dbo can ... It seems a design behaviour. ...
    (microsoft.public.sqlserver.security)
  • Re: Access 2007, SQL 2000
    ... Since users are not dbo, and SQL 2000 used the user name as the schema identifier, you have to make sure that every reference to a SQL Server object is fully qualified with the dbo. ... Normally a user should not be able to create any new objects in the db, but if they do, those new objects will be in the userName schema, not the dbo schema, assuming the user is not a db owner. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: default schema (SQL Server 2005)
    ... 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, ...
    (microsoft.public.sqlserver.security)
  • Re: exporting into a SQL Server schema
    ... inside of the schema. ... The thing that's most confusing is that the dbo schema isn't ... take a look at the way the objects in the AdventureWorks database are ... It turns out that I'm moving some tables to a web hosted solution with sql ...
    (microsoft.public.access.externaldata)
  • Re: Database connection issue using SQL schema user account
    ... They can simplify permissions insofar as being able to have ... new objects created inside of a schema inherit permissions assigned to ... --The dbo user account is not the same thing as the dbo default ... The dbo user maps to db_owner/sysadmin. ...
    (microsoft.public.dotnet.framework.adonet)