Microsoft SQL server 2019


How do i log if a table is deleted or truncated in SQL server databases?


Create a audit log file with this SQL command:

USE master

(FILEPATH = N'c:\temp\logs'


Then to make all database that start with letter A to be monitor run below SQL command:

DECLARE @command varchar(500)
SELECT @command = 'IF ''?'' LIKE ''A%'' BEGIN USE ?
EXEC(''Create database audit specification [DatabaseAuditSpecification-Truncate]
FOR SERVER AUDIT [Truncate_Audit]
ADD (Delete on database::[?] by [dbo])

EXEC sp_MSforeachDB @command

Now, do some add and delete of a table in your database to create some action to log.

Then to see the log enter this SQL command:

SELECT event_time, succeeded, object_id, object_name,session_server_principal_name,server_principal_name,server_instance_name,database_name,statement,file_name,audit_file_offset
FROM fn_get_audit_File ('c:\temp\logs\Truncate_Audit_*.sqlaudit',null,null) order by event_time DESC

You have to adjust the SQL code for your needs.

More Information:

SQL Server auditing with Server and Database audit specifications


Microsoft SQL server 2016

How do i add a AD group to all databases on the SQL server as read only access?


Adjust below script – replace AD\DBAteam with your domain\group and run it in SQL query.

Use master

DECLARE @dbname VARCHAR(50) 
DECLARE @statement NVARCHAR(max)

FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','distribution','SSISDB') 
OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @dbname 

SELECT @statement = 'use '+@dbname +';'+ ' CREATE USER [AD\DBATeam] 
FOR LOGIN [AD\DBATeam] ; EXEC sp_addrolemember N''db_datareader'', 
[AB\DBATeam] '

exec sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname 
CLOSE db_cursor 
DEALLOCATE db_cursor


The script will work until it reaches a database that is offline – there it will stop and not process any databases below that.

The users login must first be in the database;


More information:

Move or copy SQL Logins by assigning roles and permissions


Microsoft SQL server 2016

Microsoft Windows 2012 server


How add read access to all tables in all databases on one server to a user?


Add a AD user as a login to the SQL server in SSMS.

In our example we use User3, but you change it to your login-name.

EXEC sp_MSforeachdb ' Use ?; Create user User3 for login User3; Grant select on database :: ? to User3 '

In the string after the sp_MSforeachdb  command, the character ? is replaced by each database.

Use ?, this will move focus to this database, and execute the command after to this database.

Create user User3 for login User3, adds the user name to the database and connect it with the login user.

If the user3 already is in the database, then above will not work.  Then run the command again without the Create user… part.

Grant select on database :: ? to User3, will give this user READ access to all object in the database. Even new tables and views.

The grant select on a database, will give that user full read access to the database tables and views.

More information:

To grant SELECT permissions on the whole database use this:



  • <MY_USER> is user
  • <MY_DATABASE> is database name

Granting permissions on schema doesn’t help, simple reason: if new schemas occur the user will not have permissions.

Granting permissions on all objects doesn’t for the same reason, the user will not have permissions on new objects created after GRANT event.

Microsoft SQL Server 2016

Microsoft Windows 2012

How read the SQL agent jobs logs without being a sysadmin?


Add the user to a AD group and add the AD group as a login in the SQL server. In our example we will instead use SQL login User3.

In SSMS go to Security – Logins – New Login.  Create your login in SQL.

Go to your MSDB database under – Databases – System Databases – msdb – Security – Users – New User.
Add your User3 there to the msdb database.

Expand Membership and select SQLAgentReaderRole, then click OK.

Then that user can in SSMS see SQL Server Agent, and View History and properties of a SQL Agent Job.

With SQL script:

USE [msdb]
exec sp_addrolemember 'SQLAgentReaderRole', 'DomainName\GroupName'

More Information:


Microsoft Windows

git version

What is a good order of command to work with GIT?


Install GIT on your computer from here

Create a folder, where you will work with git, in our example we use C:\kurser\git-demo-example

To setup a repository, you can follow this steps:

When you are inside your git folder, you can start using GIT from tools like GIT BASH or SOURCETREE.

Git commands are case sensitive, and git commands is always lowercase. git used to that.

If you work with Azure DevOps as master source, the git function can depend on how Azure DevOps is setup.

We guess you have already clone your repo, so you can start to work.

git status  (to check you are in correct folder and branch, mostly main at start)

git pull  (to download the latest changes to the project from the remote master)

git checkout -b  “Feature/1234”  (to create a branch and start working on the files)

Work with your file in Notepad++ or Visual Studio, and save your work in the folder you have for git.

git add .  (to add all changed files to the git source control)

git commit -m “This is a updated file”   (to save the changes to your branch, add a online comment about the files)

git checkout master (switch to your local main branch to update it with any new remote changes)

git pull  (download the latest changes done by others to your main branch)

git checkout “Feature/1234”  (go back to your local branch, you been working in)

git merge master (get the files from master into your branch, any conflicts have to be solved in Visual Studio locally)

You may have to change the word master to main in your version of GIT, also remember that the branch-name is case sensitive.

git push origin “Feature/1234”   (to push your changes up to the remote git server)

Then you have to go to your remote git (or Azure DevOps) in your web browser and complete the pull there.

This picture will look different for what kind of remote repo you use. Above is from

Click on Compare & pull request button

Enter some descriptive text and click on Create pull request button.

As we are lucky, no conflicts, you can press Merge pull request. Good practice is to leave explaining comments about what the code change is about.

Click on Confirm merge button.

Recommendation is to Delete the branch, so it is not laying around.

Then you are good to go – and start over from top with your next change.

git log –oneline   (enter to see the steps you have done)

Download and install WinMerge, to have a tool to compare files.

Download and use Visual Studio Code to solve conflicts.

git reflog  (to see more detail’s of what you done)

If I accidental delete files in my local folder for the git project, i can reset my state to the one before with command:

git reset  –hard origin

This will only work if i have not committed and push the change up to remote repo.


More Information:


Microsoft Power BI local server

Microsoft Windows 2019 server


When user browse to the home or browse button in the Power BI portal they get a error like:

Could not load folder contents

You are not allowed to view this folder. Contact your administrator to obtain the necessary permissions

This when they go to http://servername/Reports/browse


In Power BI, all reports and folders, can be you set security on. The root or browse folder is the same, it is only that it is hard to find.  You need to click on “Manage Folder” to get there.

Login to PowerBI as administrator.
Go to the “root” folder by click on browse.

Then click on “manage folder ” on the right to get to the security dialog.

Inside security, click on “Add group or user” to add a group like “everyone”.

Enter the name of the group, mark “browse” and click OK.

Now this group can see this folder, and all sub-folders that do not have “customize security” set.

Recommendation, is that you on all folders and reports in the top (root) folder are having “customize security” active, so you need to manually add the users or AD groups to this folders for users to be able to view them.

Customize Security will make the folder to not inherit the security groups from the folder above.

To be able to see a sub-folder, the user need access to the top folder above as Browser.

More Information:

Microsoft SQL server 2016


SSIS job fail with error:

Could not find database ID 42, name ’42’. The database may be offline. Wait a few minutes and try again.

Check if the database have change ID.

select * from sys.databases

Above will list the database name and id.

When you do a detach and attach on a database file, you can if you are unlucky, get a new ID on the database. Then old SSIS job that worked yesterday, will fail.

Repeat the detach and attach process to get the database back to correct number.

Move file locations with ALTER command instead; this script will list the commands for each database

SELECT DB_NAME(database_id) AS [Database] 
, name AS [LogicalName]
, type_desc AS [FileType]
, physical_name AS [FilePath]
, 'ALTER DATABASE ' + DB_NAME(database_id)
+ ' MODIFY FILE ( NAME = ' + name + ', FILENAME = ''' + physical_name + '''); 'AS Command
FROM SYS.master_files
WHERE database_id > 4
ORDER BY database_id ASC, type_desc DESC;

You have to change the path to new location before you run the command.

Also copy the database files to the new location before you run the script.


More Information:

Understanding different SQL Server database states




Microsoft SQL server 2016


Some tables are not visible in SAS client program, even do the same user can see the tables in Microsoft SQL Server Management Studio (SSMS) or Tableau.

As the same user can see the tables inside Microsoft SQL Studio, it is not a permission issue.


Change the tables and views in SQL to have shorter names. SAS can not show SQL tables or views that have a name longer than 32 characters.


More Information:

Microsoft Power BI
Microsoft Windows 2016 server

You have a working Dynamic SQL query, but when you schedule it, it stop working.

Suggested solution:
Rebuild your query to not use Dynamic SQL, to make it possible to schedule it in PowerBi.

See if you can change to use fewer different data sources.

More Information:

Microsoft PowerBI report server  Version 1.10.7737.32652 (January 2021)
Microsoft Windows 2016 server

Some of the powerbi reports does not refresh from the portal or on schedule. You get a error message like this;

2022-01-19 : |ERROR|63|Error Processing Data Model Refresh: SessionId: , Status: Error Failed to refresh the model, Exception Microsoft.AnalysisServices.OperationException: Failed to save modifications to the server. Error returned: ‘COM error: Microsoft.PowerBI.ReportServer.ASEngineManagedRoot, Datasource … is not found..
COM error: Microsoft.PowerBI.ReportServer.ASEngineManagedRoot, Datasource … is not found..
The current operation was cancelled because another operation in the transaction failed.

The powerbi report can be refreshed from PowerBI for Desktop. Also the report works from a different PowerBI server.

You can check error messages in folder c:\Program Files\Microsoft Power BI Report Server\PBIRS\LogFiles


Suggested solution:

Restart the windows PowerBIReportServer service, to release memory or cache that is used by PowerBI server.


More information:

ERROR|19|Failed to get connection string for model | RequestID =  ClientSessionID =  Microsoft.PowerBI.ReportServer.AsServer.AsConnectionException: Failed to get connection string for model

error in the log file can be because you have 2 data sources in the model pointing to the same database, where the database name was all lower case in one source and upper case in the other. Use the advanced editor in PowerBI desktop to rename them to be unique and then there was one source and data refreshes fine. You must use the same letter format on all fields.