How to find all the owners of your SQL databases

Sometimes when you go true your list of databases in SQL Studio Manager you notice the owner of the database is somebody who left the company for quite some time. This can happen because the database was create while that person was logged in at that time and he automatically became the owner.

A lot of times accounts are put to disabled in the Active Directory and a scheduled to be deleted an X period later. That’s the moment that problems can arise.

It’s better to make sure your databases are owned by a service account or something like SQLadmin or SA (SQL System Admin). The query below gives you a list of all your database names with the owners so you know which once to change.

 

The Query

select suser_sname(owner_sid),name
from sys.databases

 

Tip to modify your query

If you like to see more columns for what reason so ever. Type , * in the query and you get all the columns including the header names. Now you know what the column is called and you can use that name instead of the * star. Now your query is expanded with the extra column.

 

How to Change?

Right click your database go to properties and under Files in the left column you get the option to modify the database owner in the right part of the screen.

 

 

 

 

0x80040E14 – Mysterious error

 

Yesterday my client was doing some testing on his Acceptation environment and as everything was working proper he came to my desk with a smile and frowning eyebrows.

The error he got was some kind of 0x80040E14 error so I presume it got something to do with authorizations. After hitting strait to  Google I found all kind of WSS 2.0 answers that where completely bogus. So I came back to checking the things which I should have done first anyways, “Like Logs and disc space“.

The reason for this error was that the Log directory from the SQL server run out of disc space on our SAN and because it’s an Acceptation environment it was not being monitored by anyone. I guess it turns out I will adopt this system for the future, As our storage guy said it is not necessary to monitor????

 

 

 

401 Error Integrating SharePoint and SQL Reporting Service “Set Server Defaults”

Last week I was integrating SQL Reporting Service in to SharePoint 2010 and I came across some small issues I would like to share.

The first one was that almost in every demo or manual I found most is all installed on one and the same box and the Admin password is used. Like I was splitting everything up it give me some nice puzzles.

In the old days I setup a SQL server (standalone, because Reporting and Analysis service are not cluster aware) and the reports are made on SQL with the report builder and put in a library. Here’s not much integration needed. But now  in SharePoint 2010 I decided to milk the cow and use the complete integrated mode.

My first eye opener was that I need an installed version of SharePoint 2010 and SQL on the same box. Although it might be overkill to have a complete seperate box just for report server that’s how our infra architect wanted it instead of combining SSRS with one of my app servers.

After installation I stopped all the unnecessary SharePoint services as this was just a Report and Analysis server. The only thing I thought was really weird and have not figured out yet is why I needed to install all kinds of products that I installed on my other servers like Project Server, Office Web Apps, Language Pack’s. No body is going to use the server only for PerformancePoint or true Project Server. So this is quite a mistery for me, why it’s needed but without them the Product and Config wizard it’s not running . Maybe some one out their can tell me the answer.

The second eye opener came when I tried to click “setup default server”  in the Central Administration Console. It gave me the error as seen below.

After investigating I remembered I have not set any trusted delegation in Active Directory  for Kerberos for this SQL server yet as I had done for my cluster.

So I went to the server object and in the properties (delegation tab) and I set the delegation to be trusted (the middle radio button). This resolved my issue and I could enter the set server default in Central Admin.

Two things are weird,

For My cluster I also set the SPN with the command line and I have not done that yet for this server but still it works.

• setspn -A MSSQLSvc/SVSPREPORT1 DOMAINNAME\srvSQLreport
• setspn -A MSSQLSvc/SVSPREPORT1:1433 DOMAINNAME\srvSQLreport
• setspn -A MSSQLSvc/SVSPREPORT1.domainname.local DOMAINNAME\srvSQLreport
• setspn -A MSSQLSvc/SVSPREPORT1.domainname.local:1433 DOMAINNAME\srvSQLreport

The other thing I have not set my Central Admin Console up to use Kerberos yet. only a few Web Applications. Anyways for will be a good reason but this solved my issue and hopefully yours if your reading this because you got similar problems.

How to extract Cumulative Updates and Service Packs

At the moment I’m preparing for a new initial installation and I know it’s smart to slipstream the service packs, cumulative updates and language packs. So below you find the switches on how to extract the files.

Steps

1. Extract the downloaded service pack or cumulative update to an easy folder in the root. Like, C:\abc
2. make a folder in abc like unpacked (C:\abc\Unpacked).
3. Open a command prompt, go to the easy folder (source) and type

   PackageName-KB12345-x64.exe /x:C:\abc\Unpacked

4. or if it doesn’t work (it depents on the CU or SP)try this.

   PackageName-kb12345-x64-fullfile-en-us.exe /extract:C:\abc\Unpacked

5. A small window will popup show the package is extracting.
6. Now you can copy the *.msi’s to the update folder inside the setup binary files or run it by hand.