Excel formatting SQL results

Sometimes its the little things that get annoying like requesting some information from a DBA or a system admin at a remote site where you can’t run scripts but need someone to do it for you. Quick example you want to know what servers they have across their estate and ask them to run basically SELECT @@Servername  and send the result back in excel.

SELECT @@VERSION AS [SQL Server and OS Version Info]

So you run across some servers and get the nicely formatted results

Version A

Copy and Paste to excel

Version B

and the Format is all over the shop, so a handy bit of code to have is the following. You can use it for stripping out carriage returns / line feed and tab. Just replace the @@version with whatever you want to remove the control characters from

</pre>
SELECT REPLACE(REPLACE(REPLACE(CAST(@@VERSION AS VARCHAR(MAX)), CHAR(13),''),CHAR(10),''),CHAR(9),'')   AS [SQL Server and OS Version Info]
<pre>
Control character Value
Tab char(9)
Line feed char(10)
Carriage return char(13)

and the result, nicely formatted. Simples

Version C

Advertisements
Posted in Productivity, TSQL | Leave a comment

Sysadmin Shenanigans & Lying Error messages

One of the databases that is for DBA use was in restricted user mode , this allows only certain users access to the database, basically its sysadmin users , db_owner or db_creater access to the database.

I wanted to create a table to hold some capacity data / so current DB size and Log size etc ( script in another post to come)

I got the following error message. So started to problem solve.

Msg 916, Level 14, State 1, Procedure add_operator_check, Line 123

The server principal “S-1-9-3-1619820567-1096921846-2423696570-2687005632.” is not able to access the database “Database Named Changed to protect the innocent” under the current security context.

My initial thought was its the restricted user was causing issues , as this was a production server I tried to replicate the issue on my test server without success. Tried changing the ownership of the database was user to SA and visa versa with little success.

I double checked my permissions with my favorite check quick check on production

SELECT IS_SRVROLEMEMBER('sysadmin')

Which returns 1 if I am sysadmin or 0 if I am not. Turns out I was.  I then actually read the message in a bit more detail it referenced a procedure add_operator_check

SELECT * FROM sysobjects WHERE name  like '%add_operator_check%'

This returned 0 rows. Again the error message had got me, first time I had not read it , 2nd time I had taken it literally. I checked a couple more things, ruled out it was something to do with my account, getting someone else to try.

Next steps I am sysadmin and I can’t create a table what are the possible reasons, a database trigger I thought, so I checked that.


SELECT * FROM [sys].[triggers]

Got you. Yep It had returned several rows but the one that I was interested in

Database Trigger

But the next weird thing this trigger had actually been in place over a year and hadn’t been modified. But the last table created was in the current month. Curiouser and curiouser ( Can’t believe I am quoting Alice in Wonderland and Yes I did Google that to find out)

looking at the Trigger


CREATE TRIGGER [add_operator_check]
ON DATABASE
WITH EXECUTE AS 'mdw_check_operator_admin'
FOR CREATE_TABLE

The penny starts to drop, the CREATE TABLE statement is not running as myself, sysadmin but the mdw_check_operator_admin which is to do with the management datawarehouse, which wasn’t in use. Further investigation showed this user actually doesn’t have a login to the server hence the actual message so it did actually make sense even though it wasn’t a procedure!!.

The server principal “S-1-9-3-1619820567-1096921846-2423696570-2687005632.” is not able to access the database

 

Posted in TSQL | Tagged , , | Leave a comment

SQL Server Security ( Post 2)

Have you any issues? (re-post from http://blog.solutionsctrl.com/sql-security-part-2)

Can you answer yes to all these questions:-
• Do you have a defined security policy that tells you what are acceptable settings?
• Have you and do you regularly check compliance against these?
• Do you audit system configuration changes?
• Do you get notified about these changes?
• Does the DBA audit the system?

The last one’s a difficult one, I‘m a DBA and am completely trustworthy and honest (ironically trustworthy is a setting you should have switched off). However to have a robust system – and I’ve seen attempts at a few – you should audit the administrators, but hey let’s not run before we can walk. The ones we need to worry about first off are the users that have more permissions than they need to and more than you want them to have! But let’s not get too far ahead, what we really need is a security policy.

Define the Security Policy

This is a must, else how do you know what’s acceptable and what isn’t? Best practices are best practices, it doesn’t mean they will always fit in at your organisation or company. If you veer away from them and by doing so increase your risk profile, then it should be documented with management having visibility of the risks they are carrying. It can also help you make a case to get the versions of SQL you need/want to make sure the systems are secure.

Data is a company’s business nowadays, data loss either accidental or malicious can have a detrimental impact on the running / reputation of a company.

So we need to define the rules in play:-
Define what is mandatory e.g. xp_cmdshell should not be enabled, if it is we need to know where and why. There needs to be business requirement and justifications, there are always many ways to achieve the same thing in SQL.

Define what can be optional e.g. Production environment should be segregated from development and test environments. Remember, it’s your environment, it needs to be tailored to you, there is no point in saying production should be separated if you are a small company and it will never happen as you only have one server.
Cover all aspects including:
• Standard SQL server build settings, what’s installed and not installed. What’s the standard?
• What accounts should be used? Identify the permissions required for service accounts, don’t just give them local admin, sysadmin. I have seen the same account used everywhere this is a no no.
• What settings and configurations should be switched off / on?
• Security who are the administration groups? Who needs access? Don’t under any circumstances give Sysadmin out like sweets.
Then once you have defined your security policy (this can be a work in progress) at least you’re starting to mitigate your risks and check how compliant you are. Work out the risks and start to fix them or at least highlight the risks so you can get buy in to start to remove them.
That I’ll cover next…

Posted in SQL Security | Tagged , | 1 Comment

SQL Server Security Part 1

I have several posts on the go at anyone time. I am posting come security series at blog.solutionsctrl.com but wanted to keep all the SQL stuff also on my own site so these are re-posts from solutionsCtrl with some updates that WordPress allows that Silvrback doesn’t so they might look different.  I’ll reference the original one anyway

Security part 1:- Security and bespoke TinyURL (http://blog.solutionsctrl.com/sql-security-part-1)

I have worked across a lot of SQL Server systems; seen some amazing things, applications using the SA accounts, system administrators with some seriously complex passwords (so complex you have no choice but to write it down) while the good old SQL 2000 SA password is still blank and enabled.
I have recently been involved in a piece of work around database governance and thought it would be useful to share some of the things I’ve been working on, from a complete set of Standards, Policies , Procedures for the DBA function’s roles and responsibilities to Security policies and HA strategies.

I’ll cover off some of the more useful security information. I am not a hacker but if I was I think I might have a field day as a lot of systems I’ve seen have a lot of holes in them.
As with all my posts I’ll share my working, I am a big advocate of saving time, being pro-active and through this blog series I am going to start to push this as far as I can.
The first bit is – as always – there are a lot of resources available out there. The two main ones around security that I’d recommend are the SQL 2008 R2 and SQL 2012 Security best practice whitepapers. I created some bespoke tinyurl tabs, this in itself is such a cool tool. You can give the URL your own personal name so I created SQL2008Security and SQL2012Security as shortcut
Can you imagine emailing a client the following links

http://tinyurl.com/SQL2008Security
http://tinyurl.com/SQL2012Security

or this one

https://www.google.co.uk/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0CCAQFjAAahUKEwiLkqeBrtPIAhWFzRQKHTXJADk&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2F8%2FF%2FA%2F8FABACD7-803E-40FC-ADF8-355E7D218F4C%2FSQL_Server_2012_Security_Best_Practice_Whitepaper_Apr2012.docx&usg=AFQjCNFNrqRQqLTEwRcCR5zXD1jIPSZVEw&sig2=NkMT-G9nUwUdq13raK3dwA&bvm=bv.105454873,d.bGQ

The first just looks a bit more professional no?
SQL Server as it turns out is a lot more secure than some of the other systems I could mention, but it wasn’t always the case (see blank SA password aka SQL 2000) . The later the version the more secure it is out of the box (as long as you follow best practices!!) but I don’t see that all the time.
So many times components are installed when they are not needed – Integration Services anyone?
Well I might want to use it one day, it’s easier to install upfront else I’ll have to go through change control later, any of this sound familiar?

Do you have build documentation for the SQL servers?
Are they all the same? Are you sure?
Do they have the same collations?

The same setup?
The same configuration and setup?

Next time I’ll cover Security Best practices and how you can tell if you have issues or not.

Posted in SQL Security | Tagged | Leave a comment

All Change again

I’ve been playing with SilvrBack for writing some blogs for a consultancy that I am involved with, which you can see these here posts blog.solutionsctrl.com however I always intended to blog on my own website as well.  I decided I was a bit anal ( hope that doesn’t get tagged) about the format of the SQL code, so as the content on this will be all SQL decide wordpress would be a better option, which is what this is hosted with.

I have had several blogs over the years and decided to consolidate into one. After some digging I found I could lift and shift one of those I can been blogging at allthingsql.wordpress.com ( which doesn’t exist now I have moved it) to blog.SQLServerSolutions.co.uk which is my own company SQLServerSolutions.co.uk

So let the blogging begin ( again (again))

Posted in Uncategorized | Leave a comment

Virtual Play Ground–Some Issues I came across.

I have over the course of a the last few days been setting up several VM’s as a SQL play ground there are some excellent posts from Jonathan Keyhayias here on the SQL skills website. Several things didn’t go to plan, there was some deviance from his build with more up to date version of O/S and SQL. Thought it worth cataloguing the issues ( for when I come around to it next time). I used VirtualBox from oracle.

Issues 1 – no 64 bit option in the drop down from Oracle

Solution– There is a lot out of there on this and it usually points to Virtualisation needing to be enabled in the BIOS ( usually). This can be in one of many locations I have come to find, Security tab for instance on my Dell Machine at work, My ASUS X5505c its under Misc setting in the BIOS and was enabled. When I went into “Turn features on and off “  in Windows 8.1 Hyper V wasn’t installed.  I had read the virtualisation technologies can clash, the recommendation was to disable. It wasn’t enabled though. Everything else seemed to set correctly. So I installed it, then uninstalled it. After a reboot going into creating a new VM in VirtualBox I now had the option for 64 bit Guests, Bizarre.   You also check if virtualisation is supported on your chipset with the Intel(R) Processor Identification Utility 

Issues 2 –  Me being an idiot

Solution – Er don’t be a idiot. Hindsight is a great thing, if I was going to start this from scratch again after I had completed it I would say the following to make my life easier

  • Write down all the IPaddress / Server names in a useful document . I added my to Google Keep after I was trying to work out why one machine couldn’t connect to the ISCSI connector on the SAN
  • Take snapshots after you have done things on the VM, they can be flaky and I didn’t, its not overly difficult to rebuild things just time consuming
  • Multi tasking isnt always the best thing, I installed SQL 2012 and SQL 2014 at the same time on different VM’s thinking i was saving time, turns out i wasn’t when both VM’S got the same error. (Issues 3)
  • When naming the iSCSI Network 1 and iSCSI Network 2 connections remember to actually rename them, this took some digging as one was set to just iSCSI Network.

Issues 3 – Both SQL 2012 & SQL 2014 installs Failed with  Microsoft .net framework 4.0 installation has failed with exit code 1 half way through the installation, it carried out but basically everything failed.

Solution – Download the standalone 4.5 Dot net installs. I installed the GuestAddons by attaching to the VM the following file C:\Program Files\Oracle\VirtualBox\VBoxGuestAdditions.iso This allows sharing of the local machine drives. I installed the file direct on one VM and it then downloaded the rest of the 4.5 framework, on another VM I took a different approach and copied the file into the local VHD and tried installing this didn’t work for some reason and kept throwing up an error message. Fortunately I had successfully used the file already or this would have had me scratching my head !

I’ll post subsequent errors / fixes as I come along them

Posted in virtualization | Leave a comment

Management Studio Solutions

I have found several times creating the same set of scripts for the same thing, trying to locate where I saved it, when did I last run it. I end up spending ages looking for some code I ran previously and then end up writing it again when I can’t find it anyway.

Until I started using Solutions in Management Studio. Again lots of screen shots and explanations.

Setting up solutions Click on New Project

New Solution

Enter the name of the Project and a file location. I called them “My SQL scripts” in the examples shown 

 

Filename

If you have a solution open it will ask you to save, even if you didn’t realise it, SQL has a default solution. Click no ( if you haven’t anything you want to save)

Save file yes no

 

A new solution will be created. You might need to go to the menu and select View Solution explorer if its not already open

We can now get clever with this and add new projects under this. You can now add multiple solutions to the project (so like folders to keep different script in)

 

New Project

I gave my first one a name of daily scripts

Daily Scripts

Give it a name , do this for all the sub category’s of scripts you use.

I have mocked up below a

Daily scripts

Weekly scripts

Monthly Reports

Scripts I run against particular database

As shown below I can keep scripts in this structure, the clever thing when you connect to a particular server, it remembers the connections, so the script actually opens up when double clicking on that server you last ran the script against!

Solution Explorer

The DBA team go one further and we share a combined version in source control , and we can share common scripts between us all.

solutions - My SQL 04

Save the project, by File Save MY SQL Scripts ( or what ever you called it)

Save project

Each one of the folders is a separate projects, you just need to ensure when you open the project you choose the main project.  

Open solution

You can also browse ( or create a short cut to the location of the MY SQL Scripts.ssmssln file ( Make sure it’s the correct one) as there are two. If you double click it opens management studio with all the projects

Short Cut

These can be fiddly initially setup at times ( When i first did one I deleted and started again) , but once setup and working they make life easier to keep all those scripts you use regularly !

 

 

Posted in Management Studio, Productivity | Leave a comment