June 18, 2002 at 12:52 pm
All,
I know that I can use sp_denylogin and sp_revokelogin for Windows NT or 2000 users and groups. How can I lock the logins for SQL Server Authenticated logins? Didn't there used to be a sp_locklogin system proc?
Thanks In Advance,
Darias
Edited by - dgbrown on 06/18/2002 12:55:21 PM
June 18, 2002 at 2:01 pm
It seems that you're right there is no built in way of disabling a SQL Login. I even tried running an Ad Hoc query against the xstatus column of master..sysxlogins to set the denyaccess bit (1) and unset the hasaccess bit (2), but it seems like the system is ignoring those values unless it is an NT Login.
One suggestion I can make is to open up notepad and type a whole bunch of gibberish, cut that text and paste it into the password and password confirmation boxes. Don't save a copy of the password anywhere... if you need to re-enable the account just reset the password through the sa account.
This should be sent to MS for a bug.
June 18, 2002 at 2:11 pm
Thanks for looking in to this Dan.
Darias
June 18, 2002 at 3:29 pm
Not sure its a bug, after all, doesnt changing the password solve the problem? Deny makes sense for NT logins since SA doesnt have any way to force a change on that password using SQL itself (though with appropriate domain priveleges obviously could make it happen).
Andy
June 18, 2002 at 4:23 pm
The only item that has existed is sp_denylogin to lock nt accoutns from the server but there is nothing to do so for SQL logins, either they do or don't exist. The denyaccess bit and hasaccess bit only apply to the NT accounts or groups. CHanging the password is your best answer or delete the account.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 19, 2002 at 8:36 am
I believe it's an issue because it prevents you from doing the appropriate security logging. Disabling an account is usefull for historically determining when an account was available. If you simply delete the account then you don't have any historical information about the account in your database anymore... when it was active, what it had access to, etc. Yes, you would have that information in backups but most people overwrite thier tape backups after 6 Month's or so. Changing the password is a nice workaround, but it still leaves the account active and doesn't log a date in the system for when it was disabled. I'm surprised that this setup passes the C2 security requirements.
DanW
June 19, 2002 at 8:51 am
I know that Sybase allows you to lock the logins so that they are disabled. Maybe that's what I was thinking of.
We are retiring a Dev server and I want to lock the logins so that no one is accessing that box. In the very unlikely case that the new Dev box doesn't work properly, I'd like to be able to unlock the logins and have users access the old Dev box again. Deleting the login obviously will not help in this scenario. Changing the password seems a little much for what I'm trying to do. I guess I may have to do it anyway though, since I can't lock the logins.
DB
December 4, 2016 at 4:17 am
There is simple option to DISABLE a login using:
ALTER LOGIN [LLOOGGIINN] DISABLE
GO
ALTER LOGIN [LLOOGGIINN] ENABLE
GO
Moreover we have option to DENY LOGIN which is not the same as DISABLE
GRANT CONNECT SQL TO [LLOOGGIINN]
GO
DENY CONNECT SQL TO [LLOOGGIINN]
GO
* DENY do NOT apply to sysadmins, while DISABLE do
* DISABLE cannot login, but logins can be impersonated via "execute as login = ‘login_name’ "
* There is another option of LOGIN that get locked
** The above three options can be seen in the LOGIN properties windows under the tab status.
*** In addition there is option to use sp_denylogin for windows users
Senior consultant and architect, data platform and application development, Microsoft MVP.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply