Can't delete SQL login (with MSSQL 2005, Dynamics NAV 5.0)

Question

We are running MSSQL 2005 with Navision (Dynamics NAV) 5.0.

When a user left the company, we would delete his/her logins from both SQL Server as well as NAVISION.  However, we couldn’t delete logins at SQL Server (Microsoft SQL Enterprise Manager V8.0) anymore started a few months ago.

Whenever we tried to delete a user login at SQL, it would spike up server's CPU usage to 100% for a minute or two, and then error out with below message:

"Error 1204: The SQL Server cannot obtain a LOCK resource at this time.  Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration"

I've tried doing so off business hours when no one is using NAV but still the same error.

Based on the error message, it means that there are too many records need to be updated/processed on this request so it failed.

I believe that we have too many companies set up on NAV, thus too many tables and records to go through when deleting a login.

Suggestion is to reduce number of records in the request - but how can I “reduce” number of records in login deletion request?

I’ve tried to reconfigure the allowed maximum number of LOCKS on SQL to its max, restarted MSSQL, and then attempted to delete a login, but still failed with same error.

Anyone can shred some lights in this issue?  Thanks!

Answer 1

Hi,

How you drop the Login? Via OSQL/SQLCMD commands? Via T-SQL? Or just clicking the login on SQL Server Enterprise and then right-click Delete...?

Where are you performing this operation? Right on the server console or from a client?

Sometimes GUI freezes taking a lot of resources for nothing, while SQL engine keeps behaving as a "good citizen"

(That's why I prefer command-line administration)

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 2

Hi Sebastian,

Yes, I have tried the command line too. 

Usually I could right click the login on SQL Server Enterprise at the server console and select "Delete" to delete the login. 

But since it started giving me error, I have tried the command line too: sp_dropuser "username" at SQL Query Analyzer.  It returned with same error message after freezing for a few minutes. :(

Answer 3

Hi,

Please, try the following

ALTER DATABASE xyz SET SINGLE_USER WITH ROLLBACK IMMEDIATE

USE xyz

DROP USER abc

ALTER DATABASE xyz SET MULTI_USER WITH ROLLBACK IMMEDIATE

Be aware that this script will put your database momentarily on single user mode, so please be prudent.

You'll probably have to stop the applications connecting to your database before executing it.

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 4

Hi Sebastian,

Thanks for getting back to me!

Just to confirm, so the rest of the existing user logins (both “standard” and “Windows User”) will still there after running these scripts, right?

Answer 5

Hi,

Be aware that DROP USER drops a single user from a single database.

If you want to drop a login, you need to :

a. Drop all other objects owned by this login across all databases (typically schemas)

b. Drop all users this login owns across all databases

c. Finally, drop your login

In that case, all your user databases will need to be on single_user mode.

I assume you already evaluated the impact of dropping these specifical logins.

Finally, I strongly recommend you to copy (mirror) your production server to an isolated sandbox and test this procedure there.

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 6

There are some contradictions in your post. You say that you use MSSQL 2005, but you refer to Enterprise Manager V8.0 and you also talk about Query Analyzer.

Enterprise Manager does not support connections to SQL 2005, so maybe you are using SQL 2000? Or do you mean SQL Server Management Studio? Could you post the full output from "SELECT @@version" to clarify?

I find it unlikely that dropping a login would cause any problem of the kind you describe, since there are not very many things tied to a login. Sebastian suggests that you need to drop the login from all databases first. It is certainly a good idea, but if you don't the users will just be left orphaned.

Dropping a database user could possibly require more resources, since a user can own objects. But not so much that you would run out of locks.

One possibility is that there is a DDL trigger, and it is the DDL trigger that freaks out. Do you know if there are any DDL triggers on the database/server?

I should add that I have exactly zero knowledge about Dynamics, and this applies to most people who answer questions here.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 7

Oops, sorry for the confusion!  Yes, I was using SQL Server Enterprise Manager v8.0 to delete logins.

And I just double check my SQL version and you are right, mine is Microsoft SQL Server  2000 - 8.00.760 (Intel X86).   I was under an impression that we are using 2005 all these times :X omg!

For list of logins that I need to delete, they have access to the Dynamics’ database only.

When I right-click a login from SQL Server Enterprise Manager > Properties > “Database Access” tab > uncheck Dynamics’ database from it > OK – it gave me same error message.

Then I expand Dynamics’ database > Users > right-click and select “Delete” – It gave me same error.

For DDL trigger, can you please kindly advise how can I check?

For coming long weekend, I may try Sebastian’s suggestion – set Dynamics’ database to single-user mode, see if I can drop a user, and change it back to multiple-user mode.

Answer 8

If you are on SQL 2000, there is no DDL trigger. That was a feature added in SQL 2005.

Did you try running

USE <dynamicsDB>
go
EXEC sp_dropuser 'olduser'
EXEC sp_droplogin 'oldlogin'

from Query Analyzer?

I see little reason to set the database in single-user mode to drop users.

By the way, not only are you on SQL 2000, but you are not on the latest Service Pack of SQL 2000. There is an Service Pack 4 available.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 9

Hi Erland,

On Query Analyzer, I have selected the database for Dynamics first, and then ran "EXEC sp_dropuser 'olduser'" but it gave me same error message.  So setting the database in single-user mode before running the above query doesn't seem to help?

Thanks much everyone!

Answer 10

OK. I was thinking that maybe Enterprise Manager was doing something behind the scenes.

Have you used Profiler before? I would be interested to know at which statement the command fails. I looked through the code, and I did not see anything "heavy" that could cause this. You would set up a Profiler trace, which you filter for the spid in the query window, and then you would add the events Errors:Exception and SP:StmtStarting and SP:StmtCompleted. Then we could see which exact statement that fails.

What does "sp_configure locks" return?

How much memory is there in the machine?

As I said before, I don't think single-user mode helps. But a restart of SQL Server may help.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 11

Oh, thing to try is to run SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before you run sp_dropuser. If the statement that errors out is an updating operation, it is not going to help, but it is worth a try.

---

Erland Sommarskog, SQL Server MVP, [e-mail]