Archive for September, 2014

How to resolve the users “disabled” issue?

First of all, let’s clarify this up front! There’s no such thing as disabling and enabling users in SQL Server. If you keep on communicating to other professional SQL Server DBAs that you wish to disable or enable a user, we will put you in the “ESPN C’MON MAN!” segment because you had fumbled in the 1 yard line without anyone tackling you. Make no mistake about it. There’s disabling and enabling logins. But, there’s no such thing as disabling and enabling users! What if you see the user has the “Red Down” arrow? Do not Google too much on this topic or else some of the forums will tell you to remove the user and then create it again! Don’t do it. The following will describe the symptom and the remediation.

If you see the user with the “Red Down” arrow like the following screen-shot, the most likely cause is that someone had revoked the “CONNECT” privilege from the user or someone had just performed the following steps: 1) created a login from Windows, 2) created the user in the corresponding database, 3) then added the user to a database role. In both cases, the “CONNECT” privilege is missing.
User CONNECT Permission Revoked

To reproduce this, you can use the following steps:
1. Create the test login.

-- Create Login
USE [master];
CREATE LOGIN [Billy] WITH PASSWORD=N'Bob', 
DEFAULT_DATABASE=[master], 
DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; -- this is for demo only

2. Create the test user.

USE [AdventureWorks2012];
CREATE USER [Billy];

3. Add the user to a database role. Note: for SQL Server Logins, you will not experience the “CONNECT” not granted issue as in the case for Windows Logins.

USE [AdventureWorks2012];
EXEC SP_ADDROLEMEMBER 'db_datareader', 'Billy'

4. Revoke the “CONNECT” privilege from the user. Note: you need to use [] around the user as in [Billy]. You will then see the user “disabled” with the “Red Down” arrow next to it.

USE [AdventureWorks2012];
REVOKE CONNECT FROM [Billy];

Solution:
To resolve this issue, you will just need to grant the “CONNECT” privilege to the user. Yes. That’s all she wrote!

USE [AdventureWorks2012];
GRANT CONNECT TO [Billy];

To confirm the user has access:

-- To confirm the user has access to the database
USE [AdventureWorks2012];
SELECT name, 
	hasdbaccess
FROM sys.sysusers 
WHERE name = 'Billy'

How to Send the Output of a Stored Proc in CSV Format Using Database Mail?

You have been presented with a unique and interesting challenge. The company you work for wishes to see an automated report every Monday morning at 9am based on a query result in CSV format. It does not have Reporting Services installed. We all know that we can use Database Mail to send out html reports or send out alerts. Can we send out report in CSV format? Yes we can! Here’s what you need and how.

While there are no extra software programs or components required to accomplish this task, you do need to have Database Mail and xp_cmdshell enable. In addition, you need to create a folder as the repository for the CSV files to be generated. If you have not enabled Database Mail or do not have an email account handy, see my blog on “How to setup database mail using a gmail account?

Here’s an overview of the steps required:
1. Enable Database Mail if not already done.
2. Enable xp_cmdshell if not already enabled.
3. Create the stored proc for the report you wish to get automated and scheduled to run.
4. Create the folder as the repository for the CSV files to be generated.
5. If for whatever reason you are not able to use domain login to connect to the SQL Server instance, create the required login to use SQLCMD to connect to the corresponding instance with sufficient privileges to run the stored proc.
6. Create the TSQL script to run the steps to generate the CSV file and then send it out as an attachment.
7. Schedule a SQL Server Agent job to run the TSQL script you had created in step 6.

Implementation steps:
1. Enable Database Mail if not already done.
If not already enabled, then see my blog on “How to setup database mail using a gmail account?

2. Enable xp_cmdshell if not already enabled.

-- Enable xp_cmdshell
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

3. Create the stored proc for the report you wish to get automated and scheduled to run. In this example, we will use the stored proc, [dbo].[uspGetBillOfMaterials] in AdventureWorks2012 as an example.
uspGetBillOfMaterials

4. Create the folder as the repository for the CSV files to be generated.
For this example, I have created the C:\BillOfMaterials on my laptop.

5. If for whatever reason you are not able to use domain login to connect to the SQL Server instance, create the required login to use SQLCMD to connect to the corresponding instance with sufficient privileges to run the stored proc.

USE [master]
GO

CREATE LOGIN [Billy] WITH PASSWORD=N'Bob', 
DEFAULT_DATABASE=[master], 
DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [Billy] -- this is for testing purposes only
GO

6. Create the TSQL script to run the steps to generate the CSV file and then send it out as an attachment.

DECLARE @sqlCommand VARCHAR(999)
DECLARE @filePath VARCHAR(125)
DECLARE @fileName VARCHAR(125)
DECLARE	@fileAttachment VARCHAR(225)		
DECLARE @todaysDateFormatted VARCHAR(30)
DECLARE @emailSubject VARCHAR(50)
 
SET @filePath = 'C:\BillOfMaterials\' -- folder to store the csv files to be generated

-- generate the dynamic file name based on the date and time 
SELECT @todaysDateFormatted = CONVERT(VARCHAR, GETDATE(), 112) + '_' +
         CAST(DATEPART(HOUR, GETDATE()) AS VARCHAR) + '_' +
         CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR) + '_' +
		 CAST(DATEPART(SECOND, GETDATE()) AS VARCHAR)

-- create the file name
SET    @fileName = 'BillOfMaterials_' + @todaysDateFormatted + '.csv'

-- create the report 
SET    @sqlCommand =
       'SQLCMD -S (local) -UBilly -PBob -d AdventureWorks2012 -Q "EXEC [dbo].[uspGetBillOfMaterials] 800, ''9/13/2014''" -s "," -o "' +
       @filePath + @fileName +
	   '" '
EXEC   master..xp_cmdshell @sqlCommand;

-- create the attachment
SET @fileAttachment = @filePath + @fileName;

SET @emailSubject = 'Bill of Materials Report ' + @todaysDateFormatted

EXEC msdb.dbo.sp_send_dbmail 
  @profile_name='SQLAlerts', -- change your database mail profile name here
  @recipients='jpchen@handsonsqlserver.com', -- change the recipient(s) here, separated by ;
  @subject=@emailSubject, -- email subject
  @body='Please see attachment for today''s report for Bill of Materials!', -- note to show up in the email body
  @file_attachments=@fileAttachment -- email attachment

Check your email to see the email and the report attached. Beautiful isn’t it?

CSVEmailAttachment

7. Schedule a SQL Server Agent job to run the TSQL script you had created in step 6.

REFERENCES:
sqlcmd Utility
http://msdn.microsoft.com/en-us/library/ms162773(v=sql.110).aspx

xp_cmdshell Server Configuration Option
http://msdn.microsoft.com/en-us/library/ms190693(v=sql.110).aspx

How to create maintenance jobs for SQL Server instances running on SQL Server Express Edition?

SQL Server Expression edition has the best pricing model of all editions of SQL Server- it is free! But, there are catches. It has the limitations of lack of the SQL Server Agent service, 10GB database size limit, and 1GB max memory. To see a full list of features supported, you can visit http://msdn.microsoft.com/en-us/library/cc645993(v=sql.105).aspx.

While it’s rare to see SQL Server Express Edition installation on enterprise production systems, they do happen. As a DBA, you will need to ensure the server maintenance jobs are accounted for and enabled for the SQL Server Express instances. But, without SQL Server Agent, how can you accomplish such task? Have no fear, my friends. It is not Mission Impossible.


Here’s an overview:

  1. Create the required stored procedures.
  2. Test your SQLCMD commands that you wish to execute and then save it as a batch file.
  3. Schedule the batch file to run using Task Scheduler.

Implementation steps:

  1. In this example, we will test cycling the SQL Server Error Log using SQLCMD.  Open the Command Prompt and then test running the following command:
    sqlcmd -S <i>SERVERNAME</i> -Q "EXEC master.sys.sp_cycle_errorlog"

  2. If step 1 is successful, copy and paste the command into a text file and then save it as a batch file. Eg.: CyclingSQLErrorLog.bat.

  3. Open Task Scheduler (Windows -> Administrative Tools -> Task Scheduler).

    Task Scheduler


  4. On the Actions Pane and under Task Scheduler, click “Create Basic Task…”
    Create Basic Task

  5. Name the task and then click on the “Next” button:
    Name the Task

  6. On the “Trigger” section, set the schedule and then click the “Next” button to continue:
    Set the Schedule

  7. On the “Actions” section, select the default option “Start a program” and then click the “Next” button to continue:
    Action

  8. Click on the “Browse” button and then navigate to the location where you had saved the batch file. Select the batch file and then click the button “Open” to select it. Click the “Next” button to continue.
    Browse to select the batch file

  9. On the Finish section, review the options you had selected and click the “Finish” button to complete.

  10. On the Active Tasks section, you will find the new task you had just created. Double-click to open it.
    Active Tasks

  11. On the Action Pane located on the left hand side, click on the “Properties” link:
    Properties
  12. On the task scheduler properties dialog box and on the “General” tab, click on the “Change User or Group…” button to specify the dedicated domain user with permission to run the scheduled task:
    Change Users or Group
  13. You can test running the task by right-click it and select “Run”:
    Run the scheduled task

Note:

  1. In the above example, we have only tested running the simple task to cycle the SQL Server Error Log. The same concept can apply to database backup, index maintenance, and update statistics maintenance jobs.

  2. To view all the options for SQLCMD, go to http://msdn.microsoft.com/en-us/library/ms162773.aspx.


REFERENCES:
Features Supported by the Editions of SQL Server 2008 R2
http://msdn.microsoft.com/en-us/library/cc645993(v=sql.105).aspx

How to schedule and automate backups of SQL Server databases in SQL Server Express
http://support.microsoft.com/kb/2019698

Schedule a Task
http://technet.microsoft.com/en-us/library/cc748993.aspx