数据MSSQL 2008: 删不掉的数据困扰（mssql2008删不掉）

Recently, some of our customers have encountered an annoying problem; the data in Microsoft MSSQL 2008 cannot be deleted. Our customers have to find and delete a huge amount of data in their databases, but the data cannot be deleted for some reasons.

At first, we suspected that there was a constraint in the database or that the data was locked due to SQL server running a long time. But it turned out to be a permission issue. It seems that after switching from a conventional SQL login to a Windows authenticated login, the database user no longer had delete permissions. Apparently, the users had delete permissions at some point in their lives and were now confused.

To solve this problem, we looked for anything that could be reset or changed, and it just so happened that there was a stored procedure that checks the permissions of the user’s default database. We ran the stored procedure and its output showed that the user’s default database had delete permissions.

We next checked to see if the database user had access to any other databases on the same server, and it turns out they did. However, these other databases did not have delete permissions set. So, we then set the permissions for those databases and ran the same stored procedure again and it showed that the user now had delete permissions on the other databases.

Finally, we changed the user’s default database to one of the databases they had access to, and this solved the problem. After this, the user had full delete permissions, which allowed them to delete the data in the database. Similarly, if the same database user had permissions in only one database, we would have to grant delete permissions for each other database.

We would like to share this experience and hope this information can help people that are in similar situations. For example, the following command can be used to grant delete permissions on the specified database for the specified user：

GRANT DELETE ON [Database_name] TO [username];

In short, Microsoft MSSQL 2008 may lead to data not being able to delete due to permission issues. If this happens, we can use the stored procedure to check user’s permission, and grant delete permission to the user if necessary.