We are using SQL Express and I have a problem with dbo.
On my machine if I logon to SQL Express using windows authentication
and create a new database I automatically have db_owner role membership
on the new database.
On a colleagues machine, if he logs onto his SQL Express using windows authentication
and creates a new database he does NOT have db_owner role membership
on the new database.
How come?
I have checked pretty much everythng - windows built in admins, SQL express sysadmin roles
service pack versions but I can not find any difference in setup.
What should I do to his machine/SQL Express setup so he automatically has db_owner role membership
for every newly created database?
Thanks
Charlie
Hi, this sounds strange. The database creator should have db_owner role memebership by default. Which ower is assigned to the new database ? Is it dbo ? Is your Account you are using for the creation of the database part of the sysadmin role ? Then you probably already have dbo Owner rights through the group memerbership.
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||I agree with Jens. This sounds strange, but most likely you get dbo access via sysadmin membership. I would first recommend trying the following query:
SELECT is_rolemember( 'db_owner' )
Members of db_owner as well as DBO should return 1 on the previous query.
I would also suggest trying the following query:
select name, type, usage from sys.login_token
order by type, usage, name
select name, type, usage from sys.user_token
order by type, usage, name
You should be able to see the login token as well as the user token on the current DB.
If the login token has an entry for sysadmin(sysadmin, SERVER ROLE, GRANT OR DENY) , then you should automatically be mapped to DBO on any DB. If you are not sysadmin, then pay attention to the user token.
Please let us know the results.
-Raul Garcia
SDE/T
SQL Server Engine
