1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Use stored Sql server stored procedure in vc++

Discussion in 'MFC' started by rajeswaridvssnr, Oct 30, 2006.

  1. rajeswaridvssnr

    rajeswaridvssnr New Member

    Joined:
    Oct 24, 2006
    Messages:
    45
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    Hyderabad
    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.... :)
     
  2. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,292
    Likes Received:
    365
    Trophy Points:
    83
    Are you able to execute the same at the Query window of SQL server.
     
  3. rajeswaridvssnr

    rajeswaridvssnr New Member

    Joined:
    Oct 24, 2006
    Messages:
    45
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    Hyderabad
    Yes, I am getting the same error when i am doing it in the query window of sql server also.
     
  4. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,292
    Likes Received:
    365
    Trophy Points:
    83
    Then probably you have some error in connecting to the server domain of the SQL server.
     
  5. rajeswaridvssnr

    rajeswaridvssnr New Member

    Joined:
    Oct 24, 2006
    Messages:
    45
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    Hyderabad
    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.
     
  6. rajeswaridvssnr

    rajeswaridvssnr New Member

    Joined:
    Oct 24, 2006
    Messages:
    45
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    Hyderabad
    Nobody Is there to help me further???
     
  7. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,292
    Likes Received:
    365
    Trophy Points:
    83
    Try executing the same SP on some other objects and see whats the output.
     
  8. rajeswaridvssnr

    rajeswaridvssnr New Member

    Joined:
    Oct 24, 2006
    Messages:
    45
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    Hyderabad
    I tried the same for different users in my local database as well tried using my remote database also...but the same result
     
  9. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,292
    Likes Received:
    365
    Trophy Points:
    83
    Then probably the permission issue with the sysadmin role. Check out the permissions of sysadmin.
     
  10. rajeswaridvssnr

    rajeswaridvssnr New Member

    Joined:
    Oct 24, 2006
    Messages:
    45
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    Hyderabad
    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.
     
  11. rajeswaridvssnr

    rajeswaridvssnr New Member

    Joined:
    Oct 24, 2006
    Messages:
    45
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    Hyderabad
    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... :)
     
  12. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,292
    Likes Received:
    365
    Trophy Points:
    83
    I must say its a very impressive one.
    My pleasure.
     

Share This Page