Category: SQL Server

I recently installed SQL Server Analysis Services (SSAS) and used the default settings during the installation. Unfortunately, this meant I installed it using a Deployment Mode of Multidimensional instead of Tabular.

I wanted to avoid uninstalling so I did some poking around and found there is a setting file which can be amended to change the Deployment Mode. The file is msmdsrv.ini which is in C:\Program Files\Microsoft SQL Server\MSAS15.GP\OLAP\Config if you have installed it into the default location:

Continue reading “Change SQL Server Analysis Services Deployment Mode from Multidimensional to Tabular”

I was recently doing some testing of a script I created for a client which used SQL Database Mail for sending emails. this was on my one of my demo environments, but I received an error when testing the script:

The solution I found was to grant permissions to public, which did work, but I’m not convinced this would be an approach to take on a clients live system:

GRANT EXECUTE on sp_send_dbmail TO public

On a recent project, I needed to create a new database role for some new SQL views which had been created.

Creating the role is easy enough, but to make sure all the relevant views (and there were quite a few) looked like a bigger job until I realised I could select from sys.views and generate the grant script at the same time.

In the below script, the first highlighted section is the database role and the second the start of the views to have select permissions granted (I always use a uv_ prefix for views):

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
	'GRANT SELECT ON ' + name + ' TO urpt_SalesViews' + CHAR(10) + 'GO'
FROM
	sys.views
WHERE
	name LIKE 'uv_[/highlight%'

The output of the above should be returned to text and can then be copied and pasted into a new script window.

This post is part of the series on SQL Snippets.

I occasionally need to make a backup of a SQL table and all rows within; while you can create a table and insert the content, this is the long way of doing. Instead you can use SELECT INTO to both create a table with the correct columns and also to copy the data.

In the below example I am creating a new table suffixed with the date; the first highlighted section is the new destination table to be created and the second the source table:

SELECT *
INTO IV00101_20210910
FROM IV00101

This post is part of the series on SQL Snippets.

I create a lot of SQL objects such as views, tables, stored procedures and so on. If they will be used from within Microsoft Dynamics then they can simply be assigned to the DYNSA role, but if they are for use in custom reporting or integrations then separate security will be required.

Usually for reporting, and especially for integrations, security will be as locked down as possible to minimise any possible attack vector. This can be done in SQL through a security role with only limited security granted to the necessary users.

The first step is to create the role itself; I always do this using a role with a name prefixed with urpt_ so that it can easily be identified as a custom user role:

-- creatr security role on database
CREATE ROLE [urpt_Role] AUTHORIZATION [dbo]
GO

Then we grant the relevant permissions to the new role. For a report this would mean only granting select permissions on the relevant views and tables; for an integration there may be inserts and updates granted.

-- grant permission to role on object
GRANT SELECT ON [dbo].[uv_SQLView] TO [urpt_Role]
GRANT SELECT ON [dbo].[GL20000] TO [urpt_Role]
GRANT SELECT ON [dbo].[GL30000] TO [urpt_Role]
GO

For a report, you may need to create a database user for the user or AD group:

-- create user on database
CREATE USER [DOMAIN\user]
GO

The final step is to assign the role to the relevant users or AD groups:

-- assign role to user on database
ALTER ROLE [urpt_Role] ADD MEMBER [DOMAIN\user]
GO

This post is part of the series on SQL Snippets.

I was doing some work with a client recently and they had an issue with some data in SQL Server which they needed change, but weren’t able to do it through the application.

The issue was that they had some names in a column marked as a key field which contained an apostrophe and it was causing them problems; the data should not have had an apostrophe, but they a user had managed to do it and they wanted to do an update to remove it.

There SQL was a little more limited than mine so they asked for assistance. I created the following SQL as an example for them on how data with apostrophe in can be managed.

The example shows how to insert data containing an apostrophe as well as two ways of changing data; one was doing it globally an the other just changing a specific record.

The key to this is that in Microsoft SQL Server you can use one apostrophe to escape another.

-- CREATE TEMP TABLE
CREATE TABLE #Temp(
	USERNAME VARCHAR(15)
)
GO

-- INSERT DATA INTO TEMP TABLE
INSERT INTO #Temp (USERNAME) VALUES ('AJ''ones')
GO

-- SELECT DATA TO CHECK
SELECT * FROM #Temp
GO

-- RUN ONE OF THE TWO UPDATE STATEMENTS
-- remove single quote FROM one record
UPDATE #Temp SET USERNAME = REPLACE(USERNAME, '''', '') WHERE USERNAME = 'AJ''ones'

-- remove single quote FROM all records
UPDATE #Temp SET USERNAME = REPLACE(USERNAME, '''', '') WHERE USERNAME LIKE '%''%'
GO

-- SELECT DATA TO CHECK
SELECT * FROM #Temp
GO

-- DROP TEMP TABLE
DROP TABLE #Temp
GO

This post is the series index for the series on SQL Snippets.

This series will build up through time as I post snippets of SQL Script; I’ll also go back and update some of the previously posted SQL snippets to bring them together into one index.

SQL Snippets
Manage Data Containing an Apostrophe
SELECT INTO
Create and Use Database Role to Restrict Access
Select Primary Keys for All Tables in Database
Select All Fields for All Tables In Database