July 1, 2002 at 2:14 pm
In this days I'm becaming crazy to find a solution
for my problem. In my application (VB and SQL
server) I implemented SQL login, using sql server
security to manage access to particular table,
executing script and so on.
Problem is that i need to write also a stored procedure
for application administrator that let him to delete
particular user from database.
Problem is that sp_droplogin is launchable only
if user isn't connected to db. Well chechink sp_who
and sp_who2 stored i can find if user is connected
or not, but I see also that those view isn't really
updated (or is it)? Infact if user log on via vb,
make a query and close application, sql server
doesn't know that user has log out and don't
delete the record from result set of sp_who or
sp_who2.
Question is, how can know exactly if user is
connected or not?
I thinked to do - before close application - a new logon
to db as database admin, launch a kill statement
and the close defenitively the application. But
as described, operation can brake performance of
application and it's not quite elegant.
Are there any other solutions?
Bye Andrea MORO
July 1, 2002 at 3:25 pm
Its true that if you're opening and closing the connection its hard to check to see who is in the db - sp_who is only a snapshot at the moment it ran. I think you can work around by with revoking db access, then checking for/killing the user, then dropping the login. Guess my question is if you much of a change of a user still being active with the login, why would you want to drop it?
Andy
July 2, 2002 at 1:06 am
I don't want to drop an user connected. I must to supply to administrator a way to delete user that he can create via the application. And via the application (without enter in query analazier and launch the sp_who, administrator cannot know when a user is connected or not.
Well, suppose administrator decide to delete user that is connected. When stored I made call the sp_droplogin an error occur because user is connected. And because there isn't any way (at least I don't found any one) to avoid a brutal hang up of the stored (like on error resume next in vb or the exception in oracle) the only way to prevent an error is don't generate it or better try to perferm so much check is possible. This is the reason for which I need to check if user is connected or not.
Have y any idea on how to implement the exception or the on error resume next?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply