Auto Fix Orphaned Users

Auto Fix Orphaned Users.

When you move your database to any higher version, Logins and Users are not sync.

This type of problem comes while the migraion, SQL calls that all users as Orphaned Users and we have to link it manually.

we have the script bellow to link it manually.

USE ADVENTUREWORKS2012
GO
DECLARE @Users TABLE
    (
         Id    INT IDENTITY(1, 1),
         Users VARCHAR(250)
    )
DECLARE @Rows     INT,
        @Username VARCHAR(250)

INSERT INTO @Users

SELECT DISTINCT [Name]
FROM   [Sysusers]
WHERE  Islogin = 1
       AND [Name] NOT IN ( 'guest', 'sa', 'dbo', 'public',
                           'sys', 'INFORMATION_SCHEMA' )
SET @Rows = @@ROWCOUNT
WHILE ( @Rows > 0 )
  BEGIN
      SELECT @Username = Users
      FROM   @Users
      WHERE  Id = @Rows

      EXEC Sp_change_users_login 'Auto_Fix', @Username, NULL, @Username
	  SELECT @Username
	  SET @Rows = @Rows - 1
  END

Thanks & Regards,
Nirav Gajjar

Grant permission on Mail Profile in SQL Server

Grant permission on Mail Profile in SQL Server.

In SQL Server you can grant send mail permission by granting “DatabaseMailUserRole” roles to particular user under MSDB database. But how to give permission to specific profile, follow the steps below.

Step1 : Right click on “Database Mail” and select “Configure Database Mail”.
Step2 : On a welcome wizard click on “Next”.
Step3 : On the “Select Configuration Task” select “Manage Profile Security” and click on “Next”.
Step4 : On the “Manage Profile Security” select your user that you want to grant permission. Here i have selected “standard” user.
Note : You must have a user created on your MSDB database.
Step5 : Check permission status on “Complete the Wizard” window and click on “Finish”.
Step6 : On the “Configuring” window click on “Close” to complete the process.

Thanks & Regards,

Nirav Gajjar

Increase Size of File Attachment in SQL Server

Increase Size of File Attachment in SQL Server.

Microsoft SQL Server provides attachment when you sent an email from your application.
By default maximum size for attachment is 1000000 bytes. Its less then 1 MB, we can increase this size by following the below steps.

Step1 : Right click on “DatabaseMail” and select “Configure Database Mail”.

Increase Attachmet 1
Step2 : On “Select Configuration Task” window select “View or Change system parameter” and click on “Next”.

Increase Attachment 2
Step3 : You can see “Maximum File Size (Bytes)” Parameter, By default it is 1000000 bytes and i have increased by 10000000 bytes and click on “Next”.

Increase Attachmet 3
Step4 : Check changed parameter value on the “complete the wizard” and click on “Finish”.

Increase Attachmet 4
Step5 : Check configuration status on “Configuring” window and “Close” the window.

Increase Attachmet 5
Now you can able to send attachment up to 10 MB of size.

Thanks & Regards,
Nirav Gajjar