Re: Security Problem with AD Group in SQL Server Security Logins area

From: Jasper Smith (jasper_smith9_at_hotmail.com)
Date: 11/17/04

  • Next message: Sue Hoegemeier: "Re: Administrative tasks" 
    Date: Wed, 17 Nov 2004 14:04:13 -0000

    This is because when they were members of the sytem administrator server
    role they were mapped to the dbo user in each database. Certain roles
    (db_owner and db_ddladmin) can create objects owned by dbo if it is
    explicitly specified during object creation otherwise it defaults to the
    current database user. Having lots of objects owned by different people
    makes management and administration as well as security more difficult
    (issues with ownership chaining for example). 

    -- 
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Sam" <Sam@discussions.microsoft.com> wrote in message 
news:49E28AD1-CFBB-402D-BD58-22F6D0BFC079@microsoft.com...
> Second problem - if I uncheck that Server Role and leave all the Server 
> Roles
> blank, I can use the Database Access tab to configure who has access to 
> the
> databases and what sort of rights they have (public, db_owner, 
> db_datareader,
> etc). But if I do this and then create a table, the owner of the table in 
> the
> database shows up as domain\user, not dbo. In all my other tables, I see 
> dbo
> as owner. Is this a problem, or just cosmetic? Thanks.
>
> Sam
>
> "Sam" wrote:
>
>> I have a problem - think I have the solution - just need someone to 
>> bounce it
>> off of.
>>
>> I have a Windows 2000 AD Security Group called AllUsers, whose membership
>> contains all my users. This was added to my SQL 2000 server under the
>> Security/Logins Section. Under Server Roles, it was given a system role 
>> of
>> "System Administrators." In the Database Access tab, it was given public 
>> and
>> db_datareader access to database 1, database 2 and database 3, but not to
>> database 4-6. Now we find that any members of this group have full dbo 
>> rights
>> to all databases, 1 to 6. I am 100% sure that this occuring because of 
>> the
>> system role of "System Administrators." which be definition, can perform 
>> any
>> activity in the SQL Server intsallation (such as create table, drop 
>> table,
>> etc). Basically a full DBO.
>> Please let me know if this is correct.
>> Thanks.
>>
>> Sam
>>
>>
>>
  • Next message: Sue Hoegemeier: "Re: Administrative tasks"

    Relevant Pages

    • Re: How to prevent DELETEs in a table
      ... It is the dbo database USER, not server-level groups, that determins ... It has implicit permissions that can not be denied. ... SQL Server just skips any permission validation for sysadmins. ...
      (microsoft.public.sqlserver.server)
    • Re: conflicting object names in sql server 2000
      ... On the local database server when it does the select * from ... > michael.xxx Note that michael is the owner of the database. ... it will see if there is one owned by dbo. ...
      (microsoft.public.sqlserver.server)
    • Re: conflicting object names in sql server 2000
      ... On the local database server when it does the select * from ... it will see if there is one owned by dbo. ...
      (microsoft.public.sqlserver.server)
    • Re: SQL 7.0 and MS Access permission issue
      ... The dbo user in a database is mapped to the sysadmin fixed server ... If your DSN uses integrated security, then the database user will be ...
      (microsoft.public.sqlserver.security)
    • RE: Restored to New server and cannot logon to STS_Config
      ... you must first determine the application pool identity. ... Right-click the virtual server that is running Windows SharePoint ... Click to select both the Security Administrators and the Database ...
      (microsoft.public.sharepoint.portalserver)