locked
SQL 2000 MSSECURITY DB - SQL Login Lockout RRS feed

  • Question

  • We still have a sql 2000 db server.

    Noticed that a sql login has been locked out.

    Queried the users table in mssecurity db

    deny reason "security admin issued command"

    Anybody know what this means?
    Tuesday, January 5, 2010 3:00 PM

Answers

  • Are you sure it is SQL 2000?

    In my opinion SQL Server 2000 didn't have the concept of locking a user out. SQL Server 2005 added password complexity requirements, including locking a login out that attempted too many incorrect login tries.

    using Query Editor issue the following command

    alter login loginname with password='password here' unlock
    Go

    for more info refer to
    http://msdn.microsoft.com/en-us/library/ms189828.aspx

    you can turn off password policy

    Tuesday, January 5, 2010 4:29 PM
  • If you are still sure that the database engine is SQL 200 version , then i would say the account might be denied access rather naming it as being locked out.

    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 'qwer'
    or

    EXEC sp_revokelogin 'qwer'

    To give them back access again you should use

    EXEC sp_grantlogin 'qwer'

    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

    Use sp_droplogin and sp_addlogin

    Go to the master database and run the following command to see what is the status of that login by running the below query.

    select * from master..syslogins where name like '%nameofyourlogin%'


    Thanks, Leks
    Tuesday, January 5, 2010 8:34 PM
    Answerer

All replies

  • Are you sure it is SQL 2000?

    In my opinion SQL Server 2000 didn't have the concept of locking a user out. SQL Server 2005 added password complexity requirements, including locking a login out that attempted too many incorrect login tries.

    using Query Editor issue the following command

    alter login loginname with password='password here' unlock
    Go

    for more info refer to
    http://msdn.microsoft.com/en-us/library/ms189828.aspx

    you can turn off password policy

    Tuesday, January 5, 2010 4:29 PM
  • If you are still sure that the database engine is SQL 200 version , then i would say the account might be denied access rather naming it as being locked out.

    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 'qwer'
    or

    EXEC sp_revokelogin 'qwer'

    To give them back access again you should use

    EXEC sp_grantlogin 'qwer'

    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

    Use sp_droplogin and sp_addlogin

    Go to the master database and run the following command to see what is the status of that login by running the below query.

    select * from master..syslogins where name like '%nameofyourlogin%'


    Thanks, Leks
    Tuesday, January 5, 2010 8:34 PM
    Answerer
  • Queried the users table in mssecurity db

    In SQL Server we don't have a system database called mssecurity, probably this might be a user database created by the application to store security logs. Can you confirm this?
    Vidhya Sagar. Mark as Answer if it helps!
    Wednesday, January 6, 2010 1:45 AM