Wednesday, April 29, 2015

PowerShell Script: List SQL Server Analysis Service Role Permission By Database and Cube

A while ago, I wrote a couple of blog entries in regards to using PowerShell scripts in order to list roles within the database and their members. The script can be found here and here.

Let’s try to expand on those scripts further.

List Role Permission For Each Analysis Service Database

If we look at the GUI, on the database level, we can assign the role to have one or more of the following permissions: full control, process database and read definition.

SSAS Role Properties

We can use the following PowerShell script to list role permission for each database:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

# SSAS server name variable
$SSASServerName = "ServerName"

# Try to connect to the SSAS server
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($SSASServerName)

# Object to store the result
$Result = @()

# Get the SSAS databases and loop thru each of them
foreach ($DB in $SSASServer.Databases)
{
# Get the SSAS database
$SSASDatabase = $SSASServer.Databases.Item($DB.name)

foreach ($Role in $SSASDatabase.Roles)
{
$DatabasePermission = $DB.DatabasePermissions.GetByRole($Role.ID)

$ItemResult = New-Object System.Object
$ItemResult | Add-Member -type NoteProperty -name DatabaseName -value $DB.Name
$ItemResult | Add-Member -type NoteProperty -name RoleName -value $Role.Name
$ItemResult | Add-Member -type NoteProperty -name DatabaseAdministrator -value $DatabasePermission.Administer
$ItemResult | Add-Member -type NoteProperty -name DatabaseProcess -value $DatabasePermission.Process
$ItemResult | Add-Member -type NoteProperty -name DatabaseReadDefinition -value $DatabasePermission.ReadDefinition


$Result +=$ItemResult
}

}

$Result | Select DatabaseName, RoleName, DatabaseAdministrator, DatabaseProcess, DatabaseReadDefinition | format-table * -Wrap -AutoSize | Out-String

List Role Permission For Each Cube within the Analysis Service Database

We can also define role permission for each cube within the Analysis Service database. If we look at the GUI, the basic role permissions for each cube would look like:

image

We can use the following PowerShell script to iterate the permission of each role for each cube within each database:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

# SSAS server name variable
$SSASServerName = "ServerName"

# Try to connect to the SSAS server
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($SSASServerName)

# Object to store the result
$Result = @()

# Get the SSAS databases and loop thru each of them
foreach ($DB in $SSASServer.Databases)
{
# Get the SSAS database
$SSASDatabase = $SSASServer.Databases.Item($DB.name)

foreach ($Role in $SSASDatabase.Roles)
{

# Get the SSAS cubes within the database
foreach ($Cube in $DB.Cubes)
{
$CubePermission = $Cube.CubePermissions.GetByRole($Role.ID)

$ItemResult = New-Object System.Object
$ItemResult | Add-Member -type NoteProperty -name DatabaseName -value $DB.Name
$ItemResult | Add-Member -type NoteProperty -name RoleName -value $Role.Name
$ItemResult | Add-Member -type NoteProperty -name CubeName -value $Cube.Name
$ItemResult | Add-Member -type NoteProperty -name CubeRead -value $CubePermission.Read.value__
$ItemResult | Add-Member -type NoteProperty -name CubeWrite -value $CubePermission.Write.value__
$ItemResult | Add-Member -type NoteProperty -name CubeProcess -value $CubePermission.Process
$ItemResult | Add-Member -type NoteProperty -name CubeReadDefinition_Local -value $CubePermission.ReadDefinition.value__
$ItemResult | Add-Member -type NoteProperty -name CubeReadSourceData_Drillthrough -value $CubePermission.ReadSourceData.value__

$Result +=$ItemResult
}
}
}

$Result | Select DatabaseName, RoleName, CubeName, CubeRead, CubeWrite, CubeProcess, CubeReadDefinition_Local, CubeReadSourceData_Drillthrough| format-table * -Wrap -AutoSize | Out-String

Combining the Scripts

As a bonus, below is the PowerShell script that can be used to list the role permissions for the cubes and databases within the SQL Server Analysis Services:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

$FormatEnumerationLimit = -1

# SSAS server name variable
$SSASServerName = "ServerName"

# Try to connect to the SSAS server
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($SSASServerName)

# Object to store the result
$Result = @()

# Get the SSAS databases and loop thru each of them
foreach ($DB in $SSASServer.Databases)
{
# Get the SSAS database
$SSASDatabase = $SSASServer.Databases.Item($DB.name)

foreach ($Role in $SSASDatabase.Roles)
{
$DatabasePermission = $DB.DatabasePermissions.GetByRole($Role.ID)

# Get the SSAS cubes within the database
foreach ($Cube in $DB.Cubes)
{
$CubePermission = $Cube.CubePermissions.GetByRole($Role.ID)

$ItemResult = New-Object System.Object
$ItemResult | Add-Member -type NoteProperty -name DatabaseName -value $DB.Name
$ItemResult | Add-Member -type NoteProperty -name RoleName -value $Role.Name
$ItemResult | Add-Member -type NoteProperty -name DatabaseAdministrator -value $DatabasePermission.Administer
$ItemResult | Add-Member -type NoteProperty -name DatabaseProcess -value $DatabasePermission.Process
$ItemResult | Add-Member -type NoteProperty -name DatabaseReadDefinition -value $DatabasePermission.ReadDefinition
$ItemResult | Add-Member -type NoteProperty -name CubeName -value $Cube.Name
$ItemResult | Add-Member -type NoteProperty -name CubeRead -value $CubePermission.Read.value__
$ItemResult | Add-Member -type NoteProperty -name CubeWrite -value $CubePermission.Write.value__
$ItemResult | Add-Member -type NoteProperty -name CubeProcess -value $CubePermission.Process
$ItemResult | Add-Member -type NoteProperty -name CubeReadDefinition_Local -value $CubePermission.ReadDefinition.value__
$ItemResult | Add-Member -type NoteProperty -name CubeReadSourceData_Drillthrough -value $CubePermission.ReadSourceData.value__
$ItemResult | Add-Member -type NoteProperty -name RoleMembers -value ($Role.Members | Select -ExpandProperty Name)

$Result +=$ItemResult
}
}

}

$Result | Select DatabaseName, RoleName, DatabaseAdministrator, DatabaseProcess, DatabaseReadDefinition, `
CubeName, CubeRead, CubeWrite, CubeProcess, CubeReadDefinition_Local, CubeReadSourceData_Drillthrough, RoleMembers | format-table * -Wrap -AutoSize | Out-String

Wednesday, April 22, 2015

PowerShell Script: Adding Database into Always On Group

Every once in a while, I have  a new database that I need to put into an existing Always On group. So I have decided to create a PowerShell script that would do just that. The script can be found here.

The script accepts the following parameters:

  • DBName – The name of the database that we need to put into Always On Group.
  • DAGName – The name of the Always On group
  • ReplicaNode – Either the primary replica or one of the secondary replica of the Always On Group
  • BackupLocation – Network share location in which each of the replica node can read or write the database backup

Assumptions

  • The database is located in the primary replica node of the Always On group.
  • The Always On group has already been created, but the database has not been joined to the group.

Script Logic

In summary, the script would do the following:

  • Information gathering
    • Find Always On primary replica
    • Find Always On secondary replica(s)
  • Run some validations
    • Check to see if the database exists
    • Database is not one of the system database (master, model, tempdb or msdb)
    • Database is online
    • Database is in full recovery mode
    • Database is currently not in any Always On group
  • Backup the database (full and transaction log) from the primary replica into the backup location
  • Restore the database using the full and transaction log backup onto each of the secondary replica
  • Add the database into Always On group on the primary replica and each of the secondary replica

Tuesday, April 14, 2015

Stale View

If you have non-schema bound view in SQL Server and the underlying table was updated, you potentially can have a stale view. A quick way to demonstrate this:

Let say that you have the following table:

CREATE TABLE dbo.Contact (
Id INT IDENTITY(1,1) PRIMARY KEY,
FirstName VARCHAR(10),
LastName VARCHAR(10));
GO

INSERT INTO dbo.Contact VALUES ('John', 'Doe');
INSERT INTO dbo.Contact VALUES ('Jane', 'Doe');

Then, create a simple view against the newly created table:

CREATE VIEW dbo.vwContact
AS
SELECT *
FROM dbo.Contact
GO

If we run the following query:

SELECT Id, FirstName, LastName
FROM dbo.vwContact

We would get the expected result:

image

Now, what happen if we add a new column to the Contact table:

ALTER TABLE dbo.Contact
ADD Country VARCHAR(10) NULL;
GO

UPDATE dbo.Contact
SET Country = 'USA'
WHERE Id = 1;

If we run the following query against the vwContact view:

SELECT    Id, FirstName, LastName, Country
FROM dbo.vwContact;

We would get the following error message:


Msg 207, Level 16, State 1, Line 28

Invalid column name 'Country'.


That’s odd, it looks like the view does not recognize the newly added Country column.

Ok, let say that your company got a new contact, his name is Mr. Rhino Hippopotamus. Obviously, you will need to have a bigger last name column. So you decide to change the LastName column from varchar(10) to varchar(20).

ALTER TABLE dbo.Contact
ALTER COLUMN LastName VARCHAR(20);
GO

INSERT INTO dbo.Contact VALUES ('Rhino', 'Hippopotamus', 'USA');
GO

If you try to query the view using the following query (let’s forget about the Country column for now):

SELECT Id, FirstName, LastName
FROM dbo.vwContact;

That seems to work just fine. You get the expected result:

image

But, what happen if you try to run the same query from another server, using a linked server:

SELECT Id, FirstName, LastName
FROM ServerA.TestDB.dbo.vwContact;

Instead of getting the expected result, we got the following error:


Msg 7347, Level 16, State 1, Line 1

OLE DB provider 'SQLNCLI11' for linked server 'HSQL01' returned data that does not match expected data length for column '[ServerA].[TestDB].[dbo].[vwContact].LastName'. The (maximum) expected data length is 10, while the returned data length is 12.


If we check on the column property for the LastName column of vwContact view from the server that has the linked server to the original server, using the following query, we would notice that somehow, the LastName column is still listed as having 10 characters long:

USE [master];
GO

EXEC sp_columns_ex 'ServerA', 'vwContact', 'dbo', 'TestDB', 'LastName', NULL;

Solution

So how to address the issue that we are having with stale view. There are several ways:

We could drop and recreate the view. In the above example, we can run the following query:

IF (OBJECT_ID('dbo.vwContact') IS NOT NULL)
DROP VIEW dbo.vwContact
GO

CREATE VIEW dbo.vwContact
AS
SELECT *
FROM dbo.Contact
GO

We could also run an alter view statement:

ALTER VIEW dbo.vwContact
AS
SELECT *
FROM dbo.Contact
GO

Alternatively, we could refresh the view using the following script:

EXEC sp_refreshview 'dbo.vwContact';

Tuesday, March 24, 2015

SQL Server Agent Job Last Run Time and Result

I've seen situations in which we have some SQL Server Agent jobs that are scheduled to run very rarely  (like once every year or once every half year). Either because of audit or troubleshooting, we need to find out those SQL Server Agent jobs last run time and their outcome. However, the SQL Server Agent history is being purged every few months (Notes: purging SQL Server Agent history periodically is not a bad thing, it is actually a good practice to keep the msdb database size manageable, esp. if your environment has lots of SQL Server Agent jobs, in which many of them have lots of job steps, and some of the jobs are scheduled to run frequently - every few minutes or even seconds).

Are we out of luck? Well not necessarily.

Demo

First create a SQL Agent job with the following script:

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'SQL Job Test',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Job Step 1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'select 1',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Job Step 2',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'select 2',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:


GO

Then we run the job using the following script:

USE [msdb];
GO

EXEC sp_start_job @job_name=N'SQL Job Test';
GO

Once that has been completed, there should be a job history for the SQL Server agent job:

image

Now purge the job history by running the following:

USE [msdb];
GO

EXEC dbo.sp_purge_jobhistory @job_name=N'SQL Job Test';
GO

After purging the job history, we will no longer have the entries for “SQL Job Test” job on dbo.sysjobhistory table in the msdb database.

image

If we try to query the sysjobhistory, using the following query, we would get 0 row:

USE [msdb];
GO

SELECT *
FROM dbo.sysjobhistory
WHERE job_id IN (
SELECT job_id
FROM dbo.sysjobs
WHERE [name] = N'SQL Job Test');

Or this query:

USE [msdb];
GO

EXEC sp_help_jobhistory @job_name = N'SQL Job Test';
GO

Gulp! No more history for SQL job “SQL Job Test”. But wait, if we look at the job properties, we can see when the job was last executed.

image

So the information must still be in the database. If we try to run the following query:

USE [msdb];
GO

EXEC sp_help_job @job_name = N'SQL Job Test';

We can see the job’s last run date, time and even outcome (For the definition of last_run_outcome column, you can check the following MSDN article). There’s more. When you run the sp_help_job stored procedure, you can also see each of the job step’s last run date, time and outcome.

If we investigate it further, the last run date, time and outcome are stored on sysjobservers and sysjobsteps within the msdb database. We can query them using the following:

USE [msdb];
GO

SELECT j.job_id,
j.Name,
js.last_run_outcome,
last_run_outcome_desc = CASE
WHEN js.last_run_outcome = 0
THEN 'Failed'
WHEN js.last_run_outcome = 1
THEN 'Succeeded'
WHEN js.last_run_outcome = 3
THEN 'Canceled'
ELSE 'Unknown'
END,
js.last_outcome_message,
last_run_datetime = dbo.agent_datetime(
CASE WHEN js.last_run_date = 0 THEN NULL ELSE js.last_run_date END,
CASE WHEN js.last_run_time = 0 THEN NULL ELSE js.last_run_time END),
js.last_run_duration
FROM dbo.sysjobs j
INNER JOIN dbo.sysjobservers js
ON j.job_id = js.job_id

Or if you want to see it for each of the job’s steps:

USE [msdb];
GO

SELECT j.job_id,
j.Name,
js.last_run_outcome,
last_run_outcome_desc = CASE
WHEN js.last_run_outcome = 0
THEN 'Failed'
WHEN js.last_run_outcome = 1
THEN 'Succeeded'
WHEN js.last_run_outcome = 3
THEN 'Canceled'
ELSE 'Unknown'
END,
js.last_outcome_message,
last_run_datetime = dbo.agent_datetime(
CASE WHEN js.last_run_date = 0 THEN NULL ELSE js.last_run_date END,
CASE WHEN js.last_run_time = 0 THEN NULL ELSE js.last_run_time END),
js.last_run_duration,
jst.step_id,
jst.step_name,
step_last_run_outcome = jst.last_run_outcome,
step_last_run_outcome_desc = CASE
WHEN jst.last_run_outcome = 0
THEN 'Failed'
WHEN jst.last_run_outcome = 1
THEN 'Succeeded'
WHEN jst.last_run_outcome = 2
THEN 'Retries'
WHEN jst.last_run_outcome = 3
THEN 'Canceled'
ELSE 'Unknown'
END,
step_last_run_datetime = dbo.agent_datetime(
CASE WHEN jst.last_run_date = 0 THEN NULL ELSE jst.last_run_date END,
CASE WHEN jst.last_run_time = 0 THEN NULL ELSE jst.last_run_time END)
FROM dbo.sysjobs j
INNER JOIN dbo.sysjobservers js
ON j.job_id = js.job_id
INNER JOIN dbo.sysjobsteps jst
ON j.job_id = jst.job_id

WHERE j.name = N'SQL Job Test';

Clean-up

To clean up the demo, you can run the following script:

USE [msdb];
GO

EXEC sp_delete_job @job_name = N'SQL Job Test';

GO

File Share Witness

If you have cluster environment and using the file share witness, you can use the following command line (using cluster.exe) to get the location of your file share witness:

cluster.exe res "FileShare Witness" /priv

Or if you prefer to use PowerShell, you can use:

Get-ClusterResource "File Share Witness" | Get-ClusterParameter | fl