SQL Server Performance

updating similar multiple table in different databases automatically

Discussion in 'SQL Server 2005 General Developer Questions' started by magikminox, May 16, 2008.

  1. magikminox New Member

    Hi Guys

    I have 4 databases in the same space.My users use all of them and use the same username and password to log into these 4 databases.In each of these databases,i have put a control table to allow me to keep track of all users that have to reset their passwords.
    The control table consists of the username and flag fields.When the flag is ON(1) the user is forced to reset thier password and if the flag is OFF(0) they are not.

    When a user logs into any one of these databases and they have to reset thier password,how do i make sure that all the other tables in other databases are also updated to make sure that the user is not forced to reset their password again when they log into those other databases later since they are using the same username and password for all databases.
    I am planning to use a stored procedure which i will put into all the four databases and when a user logs in and has to reset their password,that sproc is called and automatically updates all the other 3 tables in other 3 databases.
    Some SQL examples will be very appreciated.
    Thanks.
  2. gbd77rc New Member

    Hi
    You can use the 3 part object naming syntax for this.
    <datbase>.<schema>.<object>
    Example
    UPDATE Database1.dbo.Table
    SET off = 1
    WHERE username = 'user'
    You have to make sure that the stored procedure has access to all databases and tables. If necessary have a look at the WITH EXECUTE AS option.
    Regards
    Richard...
  3. moh_hassan20 New Member

    create one table in only one database , and create a view in the other 3 databases that reference that table.
    In that case, you mange only one physical table.
    apply the necessary security in these objects.

Share This Page