0

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?

1
  • You could ask him what he did (and review the application installation scripts)
    – eckes
    Mar 30 '19 at 20:57
1

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.

0

By using the listed script, the user cannot give himself server level create db rights. My next step would look to see if it's possible someone else with sysadmin or high-level permissions did that for him because they misunderstood permissions.

1
  • The person and I were the only one working on the database. As I didn't give him the permissions, there would seem no other option than him doing it... Mar 16 '15 at 9:26

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.