Unable to DROP Table - permissions issue?

Question

Hi,

I am using Microsoft SQL Server Standard (64-bit) version 11.0.7507.2. The computer running SQL Server is on a Domain. I am logged on to the SQL Server computer through a Remote Desktop connection. I believe the account I have logged on with is an Administrator account on the Domain. I am able to log on to SQL Server Management Studio using Windows Authentication.

I wanted to DROP a database using the following command:

DROP DATABASE PKYICNJ

... When I try to execute the above command, I get a message saying 'Cannot drop the database 'PKYICNJ', because it does not exist or you do not have permission.'

In the left pane of SSMS (Object Explorer), when I look in the 'Security' branch in the first level of the hierarchy below my server name, I see only the following three logins...

BUILTIN\Users
LUMINER\Domain Users
sa

... When I look under 'Server Roles', I see several server roles listed, but the only role that has a member is the 'sysadmin' role. The only member of the 'sysadmin' role is the 'sa' login.

I do not currently know the password for the 'sa' account, as this server was setup by someone other than myself who is know longer working with my company.

I have two questions:

(1) Based on what I have said, is my inability to DROP a database likely due to inadequate permissions? (I am certain that the database I am trying to drop exists)

(2) If my problem is related to inadequate permissions, is there any way I can hope to get around this if I am unable to find the 'sa' password?

I have our network / domain Administrator at my service, so if it is possible to elevate my permissions in SQL Server through Windows Authentication (without the 'sa' password), he should be able to do it. Unfortunately, he does not know the 'sa' password either.

Any suggestions will be greatly appreciated.

Thanks in advance,
Paul

Answer 1

Thank you for your response. At least the way this server is configured now, I can see that 'sa' is the only member assigned to the 'sysadmin' role.

Nah, because you are not member of sysadmin, you cannot see the other members. (There are a few builtin for services.)

Your mention that if the account I'm using is not a member of the 'sysadmin' role, I might not be able to stop SQL Server

No, this is a Windows action, and you need the appropriate Windows permission to stop and start services. I don't think you have to be Administrator, so theoretically you could have permission to start/stop services without being Administrator, but I would not hold that as likely.

In any case, it is not an acceptable situation to have a production server that no one in the organization can access. There is all reason to demand the IT firm to provide you documentation about which users/groups that have sysadmin access.

Answer 2

This sounds like you do not have administrative (or at least dbcreator) permissions on that given server.

If you just see those 3 users... it is obvious that those permissions are missing ;-)

You can recover your sa-user with this description:
https://www.sqlshack.com/recover-lost-sa-password/

But be aware that this can block other users/applications from accessing that server (if they are using the sa for access)

Answer 3

More likely that database does not exist.

Do you see it if your run:
select *
from sys.databases

Answer 4

Thank you Bjorn and Tom for your responses.

Per Tom's suggestion, I ran...

select * from sys.databases

... and the database I want to delete did show up on the list.

Bjorn - I think your suggestion sounds easy enough to try. I will most likely have to wait until the weekend though before I can temporarily stop the server and start it in single-user mode.

Just one question ... If I regain 'sysadmin' access using a newly created NewSA account, will I then be able to use this account to change the password on the 'sa' account? I will I just have to continue using the NewSA account?

I really appreciate your help with this.

Best regards,
Paul

Answer 5

Hi @Paul Kraemer ,

Welcome to Microsoft Q&A!
DROP TABLE requires the user to have one of the following:
ALTER permission on the table's schema
CONTROL permission on the table
Membership in the db_ddladmin fixed database role
Here is another article you can refer to: https://www.sqlserverlogexplorer.com/reset-sa-password-sql-server-step-by-step/

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.