Re: Default permissions on new database

All sysadmin role members are automatically "dbo" in all databases with no explicit mapping. Sysadmin role members basically impersonate "dbo" in all databases. However, the user
that creates the database is the one-and-only database owner and is actually mapped to the "dbo" user.

I recommend that you change the database owner after creating databases and blogged this last week: http://weblogs.sqlteam.com/dang/archive/2008/01/13/Database-Owner-Troubles.aspx

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Flubster" <Flubster@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:9DDE4864-947A-43E5-A3B2-CCCE800C32DD@xxxxxxxxxxxxxxxx
I notice when I create a new database within my SQL Server that the
permissions for the new database automatically adds a user (Who is configured
as sysadmin) with dbo permissions to this database.

Both within the login properties of the select user (User mapping) is listed
as Default Schema, dbo and within the permissions of the database listed as
user with connect permissions.

I have other users configured as sysadmin and they do not get this rights
(They are not expliticly listed within user mapping with dbo or permissions
as user within the database).

I've inherited this system and wonder if the user has somehow changed the
new database procedure so it changes the default permissions of new databases.

Any way to check what he's done, I can see no differences between him and
the other sysadmins but he's definatly specifically listed as a dbo on all
new tables.

Although I don't mind him having access, he's a sysadmin after all, I'd like
to make it uniform thoughout the system, i.e. using the inhertited
permissions rather than specific permissions that seem to be created when the
new database is created.

Any ideas?

Flubster

.

Relevant Pages

  • Problem is w/ .ADP..Re: SQL db Permissions for users not working
    ... You do not have SELECT permissions on the ... SysObjects system table in the database. ... figured out that qualifying the database owner (dbo in my ... >> I feel that the object owner is not dbo, ...
    (microsoft.public.sqlserver.security)
  • 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: 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: Delegate Power of God to only 1 database - How?
    ... Guess I'll have to look more closely at the permissions ... >I support the Professional Association for SQL Server ... >> permissions to only that database which can be assigned ... >>>Exactly what is this 'dBO' role you are referring to? ...
    (microsoft.public.sqlserver.security)
  • Re: Disable Sysadmin to view metadata in SQL2005
    ... given the fact that they have sysadmin rights to that box? ... If the permissions are not granular enough, ... I think it's View Any Database / View Server State/ View ... the metadata? ...
    (microsoft.public.sqlserver.security)