Wednesday, August 3, 2011

DBA: DC City Stories: SQL Server 2000 Disable User Account

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'


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'

No comments:

Post a Comment