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.
-
1Granting "dbcreator" server role would solve your requirement.– Rajesh RanjanJul 19, 2019 at 11:47
-
Can you explain the scenario where or how you see this being used?– Sir Swears-a-lotJul 20, 2019 at 10:25
Add a comment
|
2 Answers
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
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!
answered Jul 19, 2019 at 12:16
-
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
-
1Hi. 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
-
1One 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