Why is it called “SQL aus Hamburg” or “Nord DBA”?

Actually, I should have written this post years ago 😉 to shed light on the history or the genesis of this blog. But as the saying goes … it’s never too late.

At that time, I took my first initiatives and activities with the German SQL Server community, attended the first events, and made the first contacts. Unfortunately, I realized that my knowledge of the SQL Server was relatively low even after more than 10 years. I wouldn’t say I liked this myself, and I have set the goal to deal more with the product, basically, exactly the story of why I am active in the community. At the time, I was employed as a database administrator and, together with my colleague Thomas, looked after our customers’ SQL servers. Thomas knew a lot about SQL Server from other areas, and I already knew a lot, so we complemented each other wonderfully. Together we wanted to run this blog and write about our daily experiences, problems, or challenges. Since we both came from Hamburg – in a larger team spread across Germany – and it was our main task – the SQL Server – the search for a name was relatively easy …

Due to my active self-employment at that time, I already had a corresponding webspace, and the domain “SQL-aus-Hamburg.de” was quickly registered. The first blog post wasn’t long in coming, and then it got a little quieter around our blog 🙁

We had probably thought it would be easier, and the work demand grew… after such a long time; I can’t say why we didn’t write anything anymore… we just weren’t that far back then… Thomas always had good ideas and gave me the headlines, so to speak… but unfortunately, he never wrote any post himself (shouldn’t be a reproach!)… and then everything turned out differently… Thomas died unexpectedly and suddenly. So after that, I had to continue this blog under the same name.

For me, this blog is still, as you can see with the last posts on the Azure SQL Database Refresh, simply a notebook in which I review my own experiences for myself so that I might find them at some point or remind me when I was able to solve a specific problem successfully.

Why SQL-aus-HH ???

In social media, you can find me under @SQL_aus_HH; For the German-speaking reader, no problem at all to draw a connection between Hamburg and HH… initially, I had never toyed with the thought that at some point, I would also write articles in English or I even give talks at international events… so here again to clarify for the “foreigners.”

Hamburg is a Hanseatic city and therefore bears the abbreviation HH for “The Hanseatic City of Hamburg” on the German license plate.

As early as the Middle Ages, merchants earned a golden nose with long-distance trade. The Hanseatic League was particularly successful. This is what an alliance of cities and trade associations called itself about 700 years ago. The Hanseatic League was closed to simplify trade between the members, the Hanseatic cities. At that time, numerous tariffs, different currencies, and units of measurement made long-distance trade difficult.
[…]
Ultimately, only Hamburg, Bremen, and Lübeck officially bore the title of Hanseatic city. Hamburg and Bremen have partly retained their Hanseatic independence to this day. Besides Berlin, they are the only German cities that are also separate federal states. Their license plates also indicate the medieval city federation: HH stands for the Hanseatic city of Hamburg and HB for the Hanseatic city of Bremen.

http://stadtgeschichtchen.de/artikel/stadtgeschichte/was-ist-eine-hansestadt/
http://stadtgeschichtchen.de/artikel/stadtgeschichte/was-ist-eine-hansestadt/

Since this blog was supposed to be run by two people at the time, I had also thought about a name for my “personal appearance” or wanted to be a little more “creative” in connection with a favicon for the blog … than “Northern DBA” … please don’t ask me about the reasons/thoughts that led to this name … I can’t say it anymore. Still, this favicon has been around for a good 10 years as a “distinguishing mark” for this blog.

If you have any questions about the genesis of my blog … write to me.

Which SQL user has got which role and access on my SQL Server – analysis of roles & rights

Once again received an inquiry from our customer service staff, we will once again provide an evaluation …

There are situations in which we or the customer like an overview of all configured SQL users and their permissions on a SQL Server or wishes, we at the acquisition of new servers not only on the scripts from Brent Ozar draw and is the above request frequently, we have built us a corresponding statement.

Requirement:

  • all configured SQL users should be listed (not system users)
  • Allocation should be apparent (SQL user, Windows user or Windows group)
  • Which server role was assigned to the respective users?
  • When the account has been created or when was the lastly updated?

For such evaluations, the native DMVs are wonderful, in this case, there are the views:

  • sys.server_principals
  • sys.syslogins

The selection of useful displayable columns in the evaluation – so that the customer and the customer service is able to “understood” – and a speaking name of any columns results in the following script. (I haven’t reinvented the wheel, just improved for me usage 😉 )

USE master
GO
 
SELECT  p.name AS [loginname] ,
        p.type_desc ,
        p.is_disabled,
        s.sysadmin as IsSysAdmin ,
        s.serveradmin as IsServerAdmin,
        s.securityadmin as IsSecurityAdmin ,
        s.processadmin as IsProcessAdmin,
        s.setupadmin as IsSetupAdmin,
        s.bulkadmin as IsBulkAdmin,
        s.diskadmin as IsDiskAdmin,
        s.dbcreator as IsDBCreator,
        CONVERT(VARCHAR(10),p.create_date ,101) AS [created],
        CONVERT(VARCHAR(10),p.modify_date , 101) AS [update]
FROM    sys.server_principals p
        JOIN sys.syslogins s ON p.sid = s.sid
WHERE   p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
        -- Logins that are not process logins
        AND p.name NOT LIKE '##%'
        and p.name not like 'xyz\accountname' -- put your admin-accounts in here

The TSQL statement filters the result set to pure SQL users and Windows users, and Windows groups (both local and domain), by means of the restriction ‘##%’ We distinguish also relevant (for this evaluation) system user.
As all recipients of this evaluation are aware that we as DBAs always have full access (sysadmin role), we filtered out these users (or user groups)

and p.name not like 'xyz\account name'

As a result, we obtain the following table showing us clearly which account has which role:

loginname type_desc is_disabled IsSysAdmin IsServerAdmin IsSecurityAdmin IsProcessAdmin IsSetupAdmin IsBulkAdmin IsDiskAdmin IsDBCreator created update
sa SQL_LOGIN 0 1 0 0 0 0 0 0 0 04.08.2003 10.08.2013
NT SERVICE\SQLWriter WINDOWS_LOGIN 0 1 0 0 0 0 0 0 0 10.09.2012 10.09.2012
NT SERVICE\Winmgmt WINDOWS_LOGIN 0 1 0 0 0 0 0 0 0 10.09.2012 10.09.2012
NT SERVICE\MSSQLSERVER WINDOWS_LOGIN 0 1 0 0 0 0 0 0 0 10.09.2012 10.09.2012
NT SERVICE\ClusSvc WINDOWS_LOGIN 0 0 0 0 0 0 0 0 0 10.09.2012 10.09.2012
NT AUTHORITY\SYSTEM WINDOWS_LOGIN 0 0 0 0 0 0 0 0 0 10.09.2012 10.09.2012
NT SERVICE\SQLSERVERAGENT WINDOWS_LOGIN 0 1 0 0 0 0 0 0 0 10.09.2012 10.09.2012
EP\sql_serv WINDOWS_LOGIN 0 1 0 0 0 0 0 0 0 10.09.2012 10.09.2012
Attunity SQL_LOGIN 0 0 0 0 0 0 0 0 0 10/16/2012 12/23/2012
BackupHist SQL_LOGIN 0 0 0 0 0 0 0 0 0 11/15/2013 11/15/2013
domain\accountname WINDOWS_LOGIN 0 1 0 0 0 0 0 0 0 11/15/2013 11/15/2013
domain\groupname WINDOWS_GROUP 0 1 0 0 0 0 0 0 0 11/15/2013 11/15/2013

Now if you copy this table into an Excel spreadsheet, you can highlight the relevant lines visually better, so that the recipient will receive a quick and easy overview.
In accordance with our Security Baselines a customer-user may have a maximum server role “dbcreator“, therefore we could highlight the SQL user or group color (red), if they have too many rights or to highlight that the SQL user “sa” is not disabled as recommended.

Now you can insert the analysis either simply as an attachment or via copy / paste into an email and provide the respective receivers available.

Who wants to have this evaluation, if necessary at regular intervals, may create this as SQL Agent job, send output the table as HTML construct and the HTML output send per SQLMail.

Tuts on how to create such mails or SQL Server Agent jobs and formating HTML within can be found on SQLServerCentral: http://www.sqlservercentral.com/articles/T-SQL/99398/