Re: default schema (SQL Server 2005)

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/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SQL2005SecBestPract.doc#_Toc160646532

RLF

<dnlgauthier@xxxxxxxxxxx> wrote in message
news:1187292090.432045.126570@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Aug 16, 1:26 pm, "Russell Fields" <russellfie...@xxxxxxxxxx> wrote:
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<dnlgauth...@xxxxxxxxxxx> wrote in message

news:1187272016.929096.136070@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



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.- Hide quoted text -

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



.

Relevant Pages

  • Re: db_denydatawriter
    ... perhaps this also gives read write access on the database to this user? ... Resrictive permissions overrides in its own level. ... However, if she has sysadmin right, then she'll be able to modify that data. ... Is it possible she has some admin rights which override DenyWriter (though ...
    (microsoft.public.sqlserver.security)
  • Re: I still dont get it.
    ... > You can create a stored procedure to call xp_cmdshell to run a command. ... > though the originator has no such rights. ... > Russell Fields ... >> A has rights to sysadmin sproc, if I pass in the table to be created as ...
    (microsoft.public.sqlserver.security)
  • I still dont get it.
    ... A has rights to sysadmin sproc, if I pass in the table to be created as a ... which uses user A's rights and not the sprocs sysadmin rights. ... > the stored procedure, you create tables you need the user to create. ...
    (microsoft.public.sqlserver.security)
  • Re: PHP blamed for security problems
    ... > Giving even rather minimal rights will allow the manipulation of files ... > password file and run it through a password cracker. ... Imho the one to blame for should be the sysadmin for not using shadow ...
    (comp.lang.php)