1

How to create and give permission to a user in such a way that they will have full access only to the database(s) they have created not other database(s). I am using SQL Server 2016 Express.

2
  • 1
    Granting "dbcreator" server role would solve your requirement. Jul 19, 2019 at 11:47
  • Can you explain the scenario where or how you see this being used? Jul 20, 2019 at 10:25

2 Answers 2

1

Welcome to the world of SQL Server Security!

Probably the best place to start is by reading the Microsoft Securing SQL Server Guide. This covers all the basics from securing the physical SQL server to database and user permissions.

An alternative view is the SQL Server Central Stairway to SQL Server Security

0

Granting "dbcreator" server role restricts access of the login/user to the databases only created by it, until we don't grant extra permissions.

Thanks!

11
  • I disagree. Ms documentation says: "Members of the dbcreatorfixed server role can create, alter, drop, and restore any database." Jul 20, 2019 at 10:20
  • @SirSwears-a-lot Please test & comment back. The same user can create any objects within the database created by him/her. I posted my answer after validation on my environment. Jul 20, 2019 at 10:49
  • If a user can drop or restore/overwrite any db IMO that fails to achieve the Ops requirement that they cant access or interfer with other dbs. Jul 20, 2019 at 10:52
  • 1
    Hi. So i tested it, and can confirm that a user with DbCreater can interfere with any database. I initated a restore action that screwed up a db created by a different user. Therefore your answer/statement is incorrect, and does not provide a solution for OP. Jul 21, 2019 at 9:20
  • 1
    One thing that I agree, "he can backup and restore other databases. But he wouldn't be able to get into it. Jul 21, 2019 at 9:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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