-1

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 because the user is added to the database only as a user and not as a db_owner and sa is set as dbo. 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 regards Ronny

migrated from stackoverflow.com Oct 7 '16 at 2:21

This question came from our site for professional and enthusiast programmers.

  • Google what's new in SQL 2016? – ajeh Oct 6 '16 at 19:02
  • Have you considered adding the user to the model database with db_owner perms? – Nic Oct 7 '16 at 14:51
0

In sql 2016, you still can create user who can create a databas and become db_owner on the databas he creates but not be an sysadmin.

i checked in sql 2016 developer edition check with that script

use master
go

CREATE LOGIN [user1] WITH   PASSWORD=N'xxxxxxxx'  
 GO

ALTER SERVER ROLE [dbcreator] ADD MEMBER [user1]
GO

That user can login with [user1] and create database and do all CRUD operations in that database (only)

0

I had a similar issue - it was occurring on some servers but not others (by pure coincidence it was split along 2014 v 2016 lines).

In the end it turned out to be because the login I was using (with only db_creator rights) was a user in model on the affected servers, so the CREATE DATABASE statement had an error when trying to assign ownership of the new database to that login. In short, it was unable to map dbo to my login name. So instead it mapped dbo to sa, and thus left my login without any rights on the new database.

The solution in my case was to remove the user from model that related to my login name.

-1

Well the problem is not that u can't create the user with that server role, the problem is that it does not work like in 2014.

Test it. In SQL 2016. Create a user with dbcreator and public. Log in with that user. Create a database. What db role does that user you created the database with got in that database?

I have tested this on 2 versions, Standard and Express, both did work the same.

So have the dbcreate server role changed in how it works in SQL 2016 or is it just a setting or installation feature that i have missed?

-1

Problem has now dissapeared. I got a TSQL script for creating a User with dbcreate server role, then using that user to create the database and the user got the db_owner db role.

After that i ran that script i had no problem creating users and set them as Dbcreator and create database with the user and it then got to be a db_owner on the database.

Very strange problem. I'll be testing on customer server now and see if i get the same result there.

/Ronny

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.