I just created a new login using the GUI, which generates this script:
USE [master]
GO
CREATE LOGIN [tester] WITH PASSWORD=N'tester', DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
use [master]
GO
GRANT CREATE ANY DATABASE TO [tester]
GO
This login was granted to a person who wanted to make a new database. I didn't want to give him db_creator rights, so I used the code above. However, after he installed his application (in which the cration of a database is included), I check the login again, and now it has the db_creator server role! Somehow he has granted this to himself, and theoretically he could delete my databases now! I've tested this 3 times to make sure what this is indeed what is happening. I've made a trigger to prevent the deletion of databases, which he can't access, but I'm still worried.
How is this even possible and how can I prevent it?
Best Answer
Is the other person a local admin on the server? You need to look at the simplest explanation, which is he either knows the SA password, or he reset the password to the SA account, which a local admin at the server level can do.
Dbatools even has a cmdlet for it. If neither of those things is true, then he knows your password. If you keep your passwords on the same instance in a "password table" encrypted with the default encryption it is relatively easy to retrieve the clear text using any one of the 100s of programs that can do it. Apex makes one that you can download for free.
The simplest explanation isn't you have a magical SQL server that lets users with no rights grant themselves high-level rights. The simpler explanation is he figured out your password or the SA password somehow.