[rajeswaridvssnr]

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....

[shabbir]

Are you able to execute the same at the Query window of SQL server.

[rajeswaridvssnr]

Yes, I am getting the same error when i am doing it in the query window of sql server also.

[shabbir]

Then probably you have some error in connecting to the server domain of the SQL server.

[rajeswaridvssnr]

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.

[rajeswaridvssnr]

Nobody Is there to help me further???

[shabbir]

Try executing the same SP on some other objects and see whats the output.

[rajeswaridvssnr]

I tried the same for different users in my local database as well tried using my remote database also...but the same result

[shabbir]

Then probably the permission issue with the sysadmin role. Check out the permissions of sysadmin.

[rajeswaridvssnr]

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.