My question is How to lock a user account in sql server. I am calling the following stored procedure in my vc code..... /* Lock user */ IF @p_commit = 'Y' BEGIN EXEC sp_denylogin @p_operid END This is to lock a user account created in sql server but i am getting the following error.... 'Work' is not a Valid Window NT name. Give the complete name: <domain\username>. Here 'work' is one of the PI operators with sql server authentication. The same type of error i am getting when trying to unlock user using EXEC sp_grantlogin... What should i use in my stored procedure to lock or unlock my sqlserver user... Here work is one of the sql server users available with me ...... I don't want to restrict the winnt user...i want to restrict only sql server user to login to my application using sqlserver. Any suggetions are Welcome..........Thanks InAdvance....
No, I am able to connect to the sql server with those users. I have one user who has sysadmin role. I do login to the sqlserver using that user. I am trying to lock/Unlock other sqlserver users by using sp_grantlogin and sp_denylogin EXEC sp_denylogin 'work' but i am getting the following error 'Work' is not a Valid Window NT name. Give the complete name: <domain\username>. Here 'work' is one of the users with sql server authentication.
I tried the same for different users in my local database as well tried using my remote database also...but the same result
sysadmin permission to my user is looking fine. some of my findings are like this. Sql server provides 2 types of authentication to the users. 1) windows authentication with the domain name 2) sql server authentication. My user is having only sql server authentication.Because we need to work with the remote databases. so here what i want is I need to lock the access permission to one of my user not having sysadmin role. I think sp_grantlogin and sp_denylogin will work only for the windows authentication mode users. So how to lock my user having sqlserver authentication. Hope you got my point.
I got the answer to my problem........ If you're trying to block users from connecting to (an instance of) SQL Server (as opposed to a single database), the quickest trick I've found to block logins is to change their passwords. Here's a brief overview on the steps involved: For NT authentication, for a given login (whether user or group), run sp_denyLogin. To reverse this, issue sp_grantLogin. For SQL Authentication, change the password. To reverse this, reset the password back to what it should be. (If you don't know the password... well, that could get tricky.) This won't affect current connections, but it will prevent new connections from being made while the existing connections are closed down. Thanks for ur effort Shabbir...
