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