SQL Server 2000 command to disable user account

Question

I need to disable some user accounts within a sql server version 2000. the following sql command is giving me an " incorrect syntax near 'Login' " error. The user name is valid and spelled correctly so I'm wondering if the command syntax is different for version 2000.

ALTER LOGIN exampleuser DISABLE

Answer 1

SQL Server 2000 doesn't have the ALTER LOGIN statement. So to be able to disable the login you'll have to call the sp_denylogin procedure instead.

EXEC sp_denylogin 'exampleuser'

or

EXEC sp_revokelogin 'exampleuser'

To give them back access again you should use

EXEC sp_grantlogin 'exampleuser'

Note: sp_denylogin, sp_revokelogin and sp_grantlogin only works on Windows accounts and groups.

To be able to deny pure SQL Server logins, it seems like the only option is to remove that login completely with

EXEC sp_droplogin 'exampleuser'

but to enable it again, it needs to be re-created with

EXEC sp_addlogin 'exampleuser', 'examplepassword'

or just remove that logins access to the current database with

EXEC sp_revokedbaccess 'exampleuser'

Answer 2

sp_revokelogin will remove the login entry. However , this proc has been deprecated in favour of drop login

But note that both of these will not disable the user but delete the login.

Your ALTER LOGIN approach is correct

ALTER LOGIN exampleuser DISABLE;

works with sql server 2008 atleast.