Site Search:
Sign in | Join | Help
4Penny.net

SQL Server (T-SQL)

Comments and notes on SQL Server 2000, 2005, and T-SQL

February 2007 - Posts

  • SQL Server Orphaned Users

    When restoring a SQL Server backup, the users that are restored in the database are not the same as the users that were created at the server level, even though they may share the same name.  This creates a problem with websites/users/etc that try and log into these databases.  Because the accounts aren't the same, the login is rejected.

    There are several ways to fix this problem.  Sometimes it is easiest to just delete the account out of the database and recreate the account at the server level.  This only works when the user account in question hasn't created any of the objects in the database.

    If the account has created objects in the database, then create a new server login with the same name as the database login.  Run the stored procedure:

    sp_change_users_login 'update_one', 'username', 'username'

    This remaps the accounts to be the same based off of the name.

     For more info, see:

    http://support.microsoft.com/kb/274188

     

    Posted Feb 06 2007, 01:13 PM by corey with no comments
    [Edit Tags]

  • Configuring SQL database mail on a SQL 2005 server

    Take the following steps to configure SQL mail on a SQL 2005 server:

    Execute this script:

    use master
    go
    sp_configure 'show advanced options',1
    go
    reconfigure with override
    go
    sp_configure 'Database Mail XPs',1
    go
    reconfigure
    go

     In MANAGEMENT > DATABASE MAIL, right click and select CONFIGURE DATABASE MAIL

    Select SET UP DATABASE MAIL...

    Create a profile.

     Make the account a public profile. Important: in the 'Manage Profile Security' page, make your profile a 'default profile'

    Click on SQL SERVER AGENT > OPERATORS and create an operator

     

     

     

     

     

    Posted Feb 02 2007, 04:57 PM by Steve Gray with no comments
    Filed under: [Edit Tags]

  • Mail from a stored procedure in SQL 2005

    Code to send mail from a sql 2005 stored proc will look something like this:

    declare @body1 varchar(100)
    set @body1 = 'Server :'+@@servername+ ' My First Database Email '
    EXEC msdb.dbo.sp_send_dbmail @recipients='steve@4penny.net',
        @subject = 'My Mail Test',
        @body = @body1,
          @profile_name = 'db mail account',
        @body_format = 'HTML' ;

     

    Posted Feb 02 2007, 04:30 PM by Steve Gray with 3 comment(s)
    Filed under: [Edit Tags]