Post reply

use stored procedures in a cursor

  • November 22, 2006 at 6:53 am

    #62385

    hi all,

    I want to use sp_grantdbaccess for different users in a cursor. i.e. I have a table called xyz and this table has all the login names, user names and database names. A cursor is collecting these values into local variable @Loginname, @Username and @Databasename. The code which i use looks like this(dynamic t-sql).

    set @abc = 'USE @databasename EXEC sp_grantdbaccess ' + @Loginname + ', ' + @UserName

    exec @pqr = sp_executesql @abc

    I have realized that if I use the real database name instead of @databasename(local variable) it works. I would like to know whether there is any way around to use a local variable there. Because i have a situation where a single user have access to different databases.

    will be very thankful for any help.

    thanks

    claus

  • November 22, 2006 at 7:01 am

    #673439

    Maybe this will work :

    set @abc = 'USE ' + @databasename + ' EXEC sp_grantdbaccess ' + @Loginname + ', ' + @UserName

    exec @pqr = sp_executesql @abc

  • November 22, 2006 at 7:46 am

    #673459

    Dear Ninja,

    thank u very much. It is working.

    have a nice day.

    thanks once again.

  • November 22, 2006 at 7:56 am

    #673462

    HTH .

  • November 23, 2006 at 9:46 am

    #673711

    dear Ninja,

    I still have some more problems. Precisely saying i was trying to use that cursors in a trigger. But i have realized it now that one cannot use any user defined definitions directly (sp_grantdbaccess or sp_revokedbaccess) in a trigger. As i included the cursor in the trigger, which we have talked about yesterday, the trigger it is no more working properly. do u have any idea how i can proceed? Is Server agent a solution for it? If yes a bit of help will be great.(how can i over come this problem ...i mean to use sp_grantdbaccess some how out side the trigger)

    thanks in advance.

    beast regards

    clausen

  • November 23, 2006 at 9:50 am

    #673712

    Why would you want to do that exactly??  Those functions should be called when deploying the application, not during!?!?

  • November 23, 2006 at 10:27 am

    #673727

    i was trying to use it for developing a multistage security system(authorization and authentication).(for sql server authentication only)

  • November 23, 2006 at 11:12 am

    #673728

    Can you explain the process in details of what you are trying to do?

  • November 24, 2006 at 8:29 am

    #673813

    hi,

    i wanted to use trigger as a security component. i.e. use trigger for authentication and authorization.

    after catching and verifying the client login information from the profiler i wanted to let a trigger to start and precisely this trigger is supposed to decide whether the login has the permission to enter the databases.i.e. the trigger is supposed to add or delete the user to or from the database.(But it seems it wont work as user defined definitions are not allowed in a trigger..inst it? ).

  • November 24, 2006 at 8:33 am

    #673814

    I'm not an expert on that matter but this is supposed to be handled but the databases and server logins / permission.  And at design time (can have a module that alters those permission but no more).

     

    Any security guru can comment on this one!

  • November 24, 2006 at 8:39 am

    #673816

    thanks

    Is it true that sp_grantdbaccess and sp_revokedbaccess wont work in a trigger?.

  • November 24, 2006 at 8:42 am

    #673817

    Never tried it and never will.  If you can't make it work then that's your answer.  I can't stress enough that security should be build from the first moment you start thinking about your application.  Not while it is running!

  • November 24, 2006 at 8:43 am

    #673818

    And BTW I assume you know triggers on system tables don't work, and the rare times they do they are not reliable!  I know it probabely doesn't apply to your case but it's always worth mentionning.

  • November 24, 2006 at 8:47 am

    #673819

    thanks

    I know that triggers on system tables don't work.

    anyway..thanks for ur discussion.

  • November 24, 2006 at 8:56 am

    #673824

    Don't give up so soon, there must be another way of doing this task.  What problem were you trying to solve using this method?

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply