SQL Server Agent – Error – “Failed to initialize sqlcmd library with error number -2147467259”

Background

Experienced error running SQL Server Agent job that we are developing.

Error

Error Image

Error Message

Executed as user: LAB\mssql. @profileName :- DBA Mail [SQLSTATE 01000] (Message 0) @recipientsTo :- daniel@lab.org [SQLSTATE 01000] (Message 0) @subject :- Privilege Users on HRDB [SQLSTATE 01000] (Message 0) Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050) @body :- [SQLSTATE 01000] (Error 0). The step failed.

Explanation

  1. The error message states “Failed to initialize sqlcmd library with error number -2147467259.
  2. Unfortunately the error message is not precise

 

TroubleShooting

SQL Server Profiler

Trace Definition

Events Selection

Image

Trace Captured

Image

Explanation

  1. Event
    • Event Class :- User Error Message
    • TextData :- The EXECUTE permission was denied on the object ‘sp_DBRoleMembersList’, database ‘master’, schema ‘dbo’.
    • Application Name :- SQLCMD
    • Error :- 229

SQL Server Agent

SQL Server Agent Account

Let us get SQL Server Agent Account

SQL


select 
        tblSDSS.[servicename]
      , tblSDSS.[service_account]
from   sys.dm_server_services tblSDSS

Output

Explanation

  1. We have the service accounts that we are using for the SQL Server Engine and Agent

Remediation

Grant “SQL Server Agent” service account execute permission on targeted object.

SQL Server – Database Diagram – Metadata

Background

Wanted to quickly document the database objects that are provisioned when the user requests “Database Diagramming” on a database.

Please keep in mind that this is not an instance level operation.

It targets the context database.

 

List

Object Type Object Usage Code
Function
Function dbo.fn_diagramobjects Counts number of system supplied database diagram helper objects currently available SELECT [kb].dbo.fn_diagramobjects()
Stored Procedure
dbo.sp_alterdiagram Alter existing diagram update dbo.sysdiagrams ( data passed in via SP )
dbo.sp_creatediagram Create new diagram inserts into dbo.sysdiagrams ( data passed in via SP )
dbo.sp_dropdiagram Drop existing diagram  delete from dbo.sysdiagrams ( data passed in via SP )
dbo.sp_helpdiagramdefinition Return diagram’s definition select version, definition FROM dbo.sysdiagrams ( diagram_id passed in via SP )
dbo.sp_helpdiagrams Return diagram information SELECT *
FROM sysdiagrams
WHERE ( @dboLogin = 1 OR USER_NAME(principal_id) = @user)
AND ( @diagramname IS NULL OR name = @diagramname)
AND ( @owner_id IS NULL OR principal_id = @owner_id)
dbo.sp_renamediagram Rename diagram
dbo.sp_upgraddiagrams Copies diagram data from dbo.[dtproperties] into dbo.sysdiagrams
Table
dbo.sysdiagrams Contains metadata on user created diagrams

Tables

dbo.sysdiagrams

Indepth

SQL Server Profiler

Scenario

New database Diagram Requested

When a new Database Diagram is requested…

 

Database Diagram Preparation

When a database is being prepared for Database Diagram.

Summary

It appears that diagram metadata were previously stored in  dbo.dtproperties.

But, they are now stored in dbo.sysdiagrams.

Linux – Disk Utilization [ du ]

Background

Let us quickly discuss using “Disk Utilization“, du, to list disk utilization on a Linux System.

BTW, we will be targeting folder level information.

Options

Tabulated

Option Option Short Form Option Long Form Sample
Human Readable -h –human-readable K – Kilobytes
M – Megabytes
G – Gigabytes
T – Terabytes
Total -c –total du –total
Summarize -s –summarize du -s
du –summarize

Sample

Human Readable

Command


du -h *  2>/dev/null

Output

Total

Objective

  1. List sizes for the following folders and at the tail end list aggregated size, as well
    • db2inst1/sqllib/acs
    • db2inst1/sqllib/db2tss

Command


du -c db2inst1/sqllib/acs db2inst1/sqllib/db2tss 2>/dev/null | more

Output

Summarize

Summarize for specific folders

Objective
  1. Summarize sized for the following folders
    • db2inst1/sqllib/acs
    • db2inst1/sqllib/db2tss
Command

du -s db2inst1/sqllib/acs db2inst1/sqllib/db2tss 2>/dev/null | more

Output

Summarize for all folders

Objective
  1. Summarize all sub-folders
Command

du -s * 2>/dev/null | more

Output

Summarize for all folders and Ordered

Objective
  1. Summarize all sub-folders
  2. And, order the results
Command

du -s * 2>/dev/null | sort -nr

Output

References

  1. Lifewire
    • How To Find How Much Disk Space A File Or Folder Uses In Linux
      Link
  2. Stack Exchange
    • Listing directories based on size from largest to smallest on single line
      Link

Koinonia House – Chuck Missler, Ron Matsen, Dr William Welty – Revelation 12:1-5

Background

This morning stumbled upon a video posted on youtube.

The video dissects how far one should go in merging biblical and astronomical studies.

 

Videos

Was the Revelation 12:1-5 prophecy fulfilled on September 23rd?

Videos – NO

  1. Revelations 12 Sign – A Biblical Scholar’s View – The Forge with Ron Matsen #8
    • This week, Ron has a discussion with Dr William Welty on Sept 23rd 2017 and digs into the language of the scriptures. Are we indeed seeing the Revelation Sign fulfilled in the next 24 hours? Ron and William will take this all into ‘The Forge.’‘The Forge’ with Ron Matsen, is a new program from Koinonia House dedicated to hammering out the issues using God’s word.1 Thessalonians 5:21 says “Prove all things; hold fast that which is good.” Our desire is take your questions into ‘The Forge’ and hammer, sharpen, and test all things in accordance with God’s word.
    •  Videos
      • YouTube
        • September 23rd Rev 12 Sign – A Biblical Scholar’s View – The Forge with Ron Matsen #8
          Publisher :- Koinonia House
          Publish Date :- 2017-Sept-22nd
          Link
  2. September 23rd, 2017, The Great Sign – The Forge with Ron Matsen #4
    • Videos
      • YouTube
        • September 23rd, 2017, The Great Sign – The Forge with Ron Matsen #4
          Does Revelation 12 predict an astrological event? Is this event the trigger point for ‘The Rapture’? Join Ron Matsen as he takes the September 23rd 2017 prophecies into ‘The Forge.’
          Publisher :- Koinonia House
          Publish Date :- 2017-July-31st
          Link

 

Videos – Yes

  1. Scott Clarke
    • Scott Clarke on The September 23rd 2017 ‘Great Sign’ – The Forge with Ron Matsen #5
      Published :- 2017-August-3rd
      Link
    • The GREATEST End-Times SIGN – Revelation 12 | September 23, 2017 … HERE IS WHY!!
      Published :- 2017-May-15th
      Link
  2. Parable of the Vineyard
    • The Revelation 12 Sign in 5 Minutes! September 23 2017 Alignment Explained What you need to know
      Published :- 2017-June-15th
      Link

Indepth

  1. Scripture References
    • Revelations 12:1-5
      • And there appeared a great wonder in heaven; a woman clothed with the sun, and the moon under her feet, and upon her head a crown of twelve stars: ( Revelations 12:1 )
      • And she being with child cried, travailing in birth, and pained to be delivered. ( Revelations 12:2 )
      • And there appeared another wonder in heaven; and behold a great red dragon, having seven heads and ten horns, and seven crowns upon his heads.  ( Revelations 12:3 )
      • And his tail drew the third part of the stars of heaven, and did cast them to the earth: and the dragon stood before the woman which was ready to be delivered, for to devour her child as soon as it was born.  ( Revelations 12:4 )
      • And she brought forth a man child, who was to rule all nations with a rod of iron: and her child was caught up unto God, and to his throne.  ( Revelations 12:5 )
    • Genesis 1:14 ( Signs – Mazzaroth )
      • Then God said, “Let there be lights in the expanse of the heavens to separate the day from the night, and let them be for signs and for seasons and for days and years ( Genesis 1:14 )
    • Job 38:31-32 ( Signs – Mazzaroth )
      • “Can you bind the cluster of the Pleiades,
        Or loose the belt of Orion?  ( Genesis 38:31 )
      • Can you bring out Mazzaroth in its season?
        Or can you guide the Great Bear with its cubs?   ( Genesis 38:32 )
  2. Timeline
    • Sept 23rd, 2017
  3. Conclusion?
    • Woman & Child
  4. Signs in the Sky
  5. Interpretation Assumptions
  6. William P. Welty, Ph.D.
    • Studied Under
      • Walter Kaiser
      • Gleason Archer
  7. Witnessing to All Nations, yet fall away ( Matthew 24:10-12 )
    • At that time many will fall away and will betray and hate one another
    • and many false prophets will arise and mislead many.
    • Because of the multiplication of wickedness, the love of most will grow cold.… ( matthew 24:12 )
  8. International Standard Version Bible
    • ISV Foundation
      Link
  9. Blood Moon?
  10. Vices
    • Forsaken grammatical interpretation of scripture
    • Hate and avoid biblical studies and systematic theology
    • Mirror Emergent Church
    • What will the normal guy understand the scripture to mean
    • Wicked imagination leads people down the errored path
  11. Biblical Studies
    • Hebrew Language
      • Hebrew is very action oriented
      • Take a look at the Verbs first and then the Nouns
      • We have to look at what is being done first
      • And, then look at what that is applied to
    • What is being governed
      • Govern day from night
    • What are stars for
      • Stars given to navigate ocean
      • Liturgical calendar
        • Feast
        • Yon Kippur
        • Day of atonement
      • Accuracy
        • Can be calculated forward
        • And, backward
      • God saw the the fall
        • And, before creating man
        • He created the calendar first
  12. Use of calendar is a biblical no-no
    • Those who practice astrology as a way to align God’s calendar are on fool’s errand
  13. God never used Stars to indicate prophetic timeline
  14. Magi
    • Mixed up babylon and jewish religion
      • Daniel 2:1-2
        • In the second year of the reign of Nebuchadnezzar, Nebuchadnezzar had dreams; his spirit was troubled, and his sleep left him.
        • Then the king commanded that the magicians, the enchanters, the sorcerers, and the Chaldeans be summoned to tell the king his dreams.
    • Godly jews left behind after Babylon
    • Pagan Worship
    • Bethlehem Star
      • Is it an astronomical body?
      • Or something else
    • Prophet?
      • Why would a prophet who has a “Thus saith the Lord” need to use astronomical signs
  15. God’s Light
    • God was so mad at Herod and he did not want to have to say anything to him
    • And, he so his presence left the madi
    • Once the madi got back to their way the star’s returned
  16. Revelations
    • Apocalyptic Literature
      • God takes a complex subject and makes it accessible to Mac users like you and me
      • Instead of giving you complex instructions, he gives you symbolic data
      • Why No details
        • Promised
          • God has never promised he will give full details
        • Hostile Jamming
          • God is not bounded to detail
          • And, thus his hope can not be forfeited
          • He anticipates Hostile Jamming and so he encodes it

 

Summary

  1. Big Question Mark
    • New Heresy? Astro-Eschatology
    • Umbilical and frankly occultic amalgamation of babylon mystery
  2. Witnessing 24:11
    • Many will be disappointed
    • Not a minor movement
    • Many scandals
    • Twisting of the scripture
      • Sell books
      • Until of course the day after
  3. Blood Moon Controversy

SQL Server – Database Diagram Permissions – “Day to Day”

Background

In an earlier post, SQL Server – Database Diagram Permissions ( Link ), spoke about veeting the need to grant db_owner privilege to allow users to diagram databases.

Exercise

Create Login & Grant Access to DB

Create a new login and granted that login access to the targeted database.

In our case, our target db is WideWorldImportersDW.

User without any object level access

Here is what is available to a user who has not been assigned object level permissions.

 

 

Grant user access to targeted objects

Script


USE [WideWorldImportersDW]
GO

grant select on [Fact].[Sale] to [ls]
go

grant select on [Dimension].[City] to [ls]
grant select on [Dimension].[Customer] to [ls]
grant select on [Dimension].[Date] to [ls]
grant select on [Dimension].[Employee] to [ls]
grant select on [Dimension].[Stock Item]  to [ls]
go

Diagram Objects – Post User granted object level access

Here is the view once user has been granted object level access.

Diagrammed Objects

Here is our objects diagrammed.

Summary

Proved that once a database is configured with database diagramming objects, users are able to diagram any objects they have been assigned read permissions.

 

SQL Server – Database Diagram Permissions

Request

Last Friday I received a new request.

Here is the request:

Image

Textual

Hi Daniel,

I created this ticket to get “DB owner” access for the REDWOOD* DBs, I need this access so I can create DB diagrams.
Let me know if you need any additional info.

Docs

Googled to make sure db_owner privileges is indeed needed.

And, confirmed it is needed and so went ahead and granted it.

Listed below is the referenced documentation.

Understanding Database Diagram Ownership (Visual Database Tools)

Here is the referenced work.

Image

Textual

  1. To use Database Diagram Designer it must first be set up by a member of the db_owner role (a role of Microsoft SQL Server databases) to control access to diagrams. Each diagram has one and only one owner, the user who created it.
  2. Although any user with access to a database can create a diagram, once the diagram has been created, the only users who can see it are the diagram’s creator and any member of the db_owner role.
  3. Ownership of diagrams can only be transferred to members of the db_owner role. This is only possible if the previous owner of the diagram has been removed from the database.
    If the owner of a diagram has been removed from the database, the diagram will remain in the database until a member of the db_owner role attempts to open it. At that point the db_owner member can choose to take over ownership of the diagram.

Scenario

Good weekend.

And, with a rested mind wanted to review the steps the user followed and how the system responds without db_owner permission.

 

New Database Diagram

Request to create database diagram

Is NOT db_owner

Here is what happens when the user is not a db_owner.

SSMS

Image

Textual

A member of the db_owner role must use the database diagramming functionality in order to setup the required database diagramming objects on the SQL Server.

 

SQL Server Profiler

Image

Textual
  1. SELECT [DBLAB].dbo.fn_diagramobjects()
    • Error :- Cannot find either column “DBLAB” or the user-defined function or aggregate “DBLAB.dbo.fn_diagramobjects”, or the name is ambiguous.
  2. exec sp_executesql N’SELECT dtb.compatibility_level AS [CompatibilityLevel],
    dtb.name AS [DatabaseName2] FROM master.sys.databases AS dtb WHERE
    (dtb.name=@_msparam_0)’,N’@_msparam_0 nvarchar(4000)’,@_msparam_0=N’DBLAB’
  3. SELECT CONVERT(bit,IS_MEMBER(‘db_owner’))
Explanation
  1. Issue query against dbo.fn_diagramobjects scalar function
  2. Determine database’s compatibility level
  3. If current user is member of db_owner

 

Is db_owner

Here is what happens when the user is a db_owner.

Image

Textual

This database does not have one or more of the support objects required to use database diagramming. Do you wish to create them?

 

SQL Server Profiler

Image

Textual
  1. SELECT [DBLAB].dbo.fn_diagramobjects()
    • Error :- Cannot find either column “DBLAB” or the user-defined function or aggregate “DBLAB.dbo.fn_diagramobjects”, or the name is ambiguous.
  2. exec sp_executesql N’SELECT dtb.compatibility_level AS [CompatibilityLevel],
    dtb.name AS [DatabaseName2] FROM master.sys.databases AS dtb WHERE
    (dtb.name=@_msparam_0)’,N’@_msparam_0 nvarchar(4000)’,@_msparam_0=N’DBLAB’
  3. SELECT CONVERT(bit,IS_MEMBER(‘db_owner’))
  4. execute as user = N’dbo’;
    revert;
Explanation
  1. Issue query against dbo.fn_diagramobjects scalar function
  2. Determine database’s compatibility level
  3. If current user is member of db_owner
  4. Execute as dbo
  5. Revert

Objects Created

Once logged in as sysadmin opted to create diagramming objects, here are the objects created and permissions granted.

Details

  1. Create Database Objects
    • Create Stored Procedure ( SP )
      • dbo.sp_upgraddiagrams
      • dbo.sp_helpdiagrams
      • dbo.sp_helpdiagramdefinition
      • dbo.sp_creatediagram
      • dbo.sp_renamediagram
      • dbo.sp_alterdiagram
      • dbo.sp_dropdiagram
    • Create Function
      • dbo.fn_diagramobjects
    • Create Table
      • dbo.sysdiagrams
      • dbo.sysdiagram_properties
  2. Add Extended Property
    • For each object created, please add extended property
  3. Clean Up tasks documented
    • Though obviously not acted upon, the objects that should be removed to rollback the changes are listed

Tabulated

Here is a tabulated view of the objects created.

Object Type Object Name Permission
Procedure
dbo.sp_upgraddiagrams
dbo.sp_helpdiagrams Grant execute to public;
Deny execute to guest;
dbo.sp_helpdiagramdefinition Grant execute to public;
Deny execute to guest;
dbo.sp_creatediagram Grant execute to public;
Deny execute to guest;
dbo.sp_renamediagram Grant execute to public;
Deny execute to guest;
dbo.sp_alterdiagram Grant execute to public;
Deny execute to guest;
dbo.sp_dropdiagram Grant execute to public;
Deny execute to guest;
Function
dbo.fn_diagramobjects Grant execute to public;
Deny execute to guest;
Table
dbo.sysdiagrams
dbo.sysdiagram_properties

 

Summary

Here I am granting db_owner permission to a user so she can diagram databases.

Even worse Opened up a Connect Item.

Thanks goodness for a long weekend.

Wish I could blame it on the smoke, but then things cleared up on Sunday.

All the faults on a lazy Friday.

Could have and should have waited on a Monday and confirmed that once the dba provisioned the targeted database, db_owner is likely not needed.