October 6, 2016 at 2:38 am
Hi
I have run into something i can't find any answer to and maybe you can help me.
In SQL 2012 and 2014, if a user have the server role dbcreator and public and create a databas that user got db_owner db role on the created database and could do what ever he needed to with that database.
In SQL 2016 this does not happen and the database you create gets inaccessable .
Does anyone know of any changes in the roles between 2012/2014 and 2016 regarding this and how do i solve it, what server role does, the lowest possible, a user need to have go become db_owner on the database it creates?
I need a user, that can create a databas and become db_owner on the databas he creates but not be an sysadmin.
Best regars
Ronny
October 6, 2016 at 10:32 am
when a user has the dbcreator server role and they create a database they are set as the database owner. This allows the user to inherit the DB_Owner database role and the user maps in via the builtin dbo aqccount. What is it you are seeing\ not seeing.
This query will show the mapping
use [yourdb]
GO
select s.name, d.name
from sys.server_principals s inner join sys.database_principals d
on s.sid = d.sid
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 6, 2016 at 10:59 am
Well yes, that is true when it comes to SQL 2014 and earlier, but in SQL 2016 this does not seems to be true, hence my question if the system role dbcreator has changed in SQL 2016 compaired to earlier version.
The answer to you question:
In SQL 2016 the user just becomes a user and sa becomes the owner of the database created by the user.
But in SQL 2014 and earlier the user becomes the owner of the database it creates as u describe.
when a user has the dbcreator server role and they create a database they are set as the database owner. This allows the user to inherit the DB_Owner database role and the user maps in via the builtin dbo aqccount.
Have you tested to create a database in SQL 2016 with a user that only have the dbcreator and public server roles and got any other result?
Is there a new setting that i have to change for this behavior to change back to how it worked in SQL 2014?
/Ronny
October 6, 2016 at 2:53 pm
I don't know exactly what's going on with your issue but did note what you are trying to do in the first post.
If you use dbcreator server role, members can create, alter, drop, and restore any database. That seems a bit excessive for what you want and doesn't really follow least privileges concept.
I would think you might want to look at granting create any database permissions instead. From what you posted, this may be a better option.
Sue
October 7, 2016 at 3:31 am
roneri75 (10/6/2016)
Have you tested to create a database in SQL 2016 with a user that only have the dbcreator and public server roles and got any other result?Is there a new setting that i have to change for this behavior to change back to how it worked in SQL 2014?
/Ronny
yes I have and it works as I expect
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 7, 2016 at 4:49 am
Yes that is true, but i ment on a db role level, not as lowest level possible in sql.
But thank you anyway.
/Ronny
October 7, 2016 at 4:57 am
This problem has now dissapeard...
Yesterday i got a TSQL script from another forum that created the user with dbcreator role and the used that user to create a database.
That script somehow fixed my problem.
I can now create a new user and set the dbcreator role and then create a database with that user and that user gets the db_owner db role in that database...
Since i had tested this on 2 machines, one with Windows server and SQL std 2016 and one on a Windows 10 wth SQL express 2016 with the same resulti was very confused.
So my problem, as best i can quess, is that when u install SQL server and u create the first sql user and give him the dbcreator servernrole you get this problem.
i would like to test this a few more times to see if it happens and if so report it to MS, but since i have no time, i atleast know how to solve it.
Thanks for the "help" anyway.
/Ronny
October 7, 2016 at 5:56 am
use my query above to check the mapping, this will confirm the account marked as the database owner
select s.name, SUSER_SNAME(s.owner_sid)
from sys.databases s
where s.database_id = DB_ID('yourdb')
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply