SQL Azure User Permissions

 New Demand to find out the user permissions in Azure SQL Database. 

SELECT    roles.principal_id                            AS RolePrincipalID

  ,    roles.name                                    AS RolePrincipalName

  ,    database_role_members.member_principal_id    AS MemberPrincipalID

  ,    members.name                                AS MemberPrincipalName

FROM sys.database_role_members AS database_role_members  

JOIN sys.database_principals AS roles  

  ON database_role_members.role_principal_id = roles.principal_id  

JOIN sys.database_principals AS members  

  ON database_role_members.member_principal_id = members.principal_id;  

GO

Reference-> SQLShack

https://www.sqlshack.com/database-level-roles-in-azure-sql-database/#:~:text=Expand%20the%20Azure%20SQL%20DB,%3E%20Roles%20%2D%3E%20Database%20Roles.

Find the Jobs that failed

 Well need to send out notifications for the job failures in the SQL Server. 

Well first of all we need to look into the jobhistory to get the last completed instance of the job run completed in last 24hours. 

SELECT j.name,MAX(h.instance_id) instance_id 

FROM msdb.dbo.sysjobhistory h

INNER JOIN msdb.dbo.sysjobs j ON j.job_id=h.job_id

WHERE j.enabled=1

AND msdb.dbo.agent_datetime(h.run_date,h.run_time)> GETDATE()-1

AND h.step_id=0

GROUP BY j.name

Now that we have the instance id of the jobhistory per job that we are interested in, we can now filter the other columns that required for the reporting. 

SELECT j.name,h.message,msdb.dbo.agent_datetime(h.run_date,h.run_time) AS starttime FROM msdb.dbo.sysjobhistory h

INNER JOIN msdb.dbo.sysjobs j ON j.job_id=h.job_id

INNER JOIN 

(

SELECT j.name,MAX(h.instance_id) instance_id 

FROM msdb.dbo.sysjobhistory h

INNER JOIN msdb.dbo.sysjobs j ON j.job_id=h.job_id

WHERE j.enabled=1

AND msdb.dbo.agent_datetime(h.run_date,h.run_time)> GETDATE()-1

AND h.step_id=0

GROUP BY j.name) b ON b.name = j.name AND b.instance_id = h.instance_id

WHERE j.enabled=1

AND h.run_status =0

Hope you enjoyed the query. 

Thanks,

Tushar 

Interested in Jobs missing Schedules

Hi Peeps, 

This will be a running post.. 

I will keep updating the posts as I create the query.. 

so let's begin 

 Question-> We need to find the jobs which are missing the run in the schedule. 

 Now what do we need to get this into action. 

 a. Job is currently running flag 

b. Job Start Time 

c. Next Schedule Date/Time 

d. Job End Time 

e. If Job End Time> Next Schedule Date/Time || If Schedule Date/Time is passed and Job Start Date Time is not equal to Schedule Date/Time 

 a. Job is curently running flag

 This can be extracted from sysjobactivity and sysjobs 

 SELECT sj.Name, 

CASE

WHEN sja.start_execution_date IS NULL THEN 'Not running'

WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running'

WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'Not running'

END AS 'RunStatus',sja.job_id,sja.start_execution_date,sja.stop_execution_date

FROM msdb.dbo.sysjobs sj

JOIN msdb.dbo.sysjobactivity sja

ON sj.job_id = sja.job_id

WHERE session_id = (

SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity);

b. Job Start Time can also be pulled from SubQuery1 

c. Next Schedule Date/Time 

This we can pull from sysjobschedules 

SubQuery2 

SELECT j.job_id,convert(NVARCHAR(24),(msdb.dbo.agent_datetime(s.next_run_date,s.next_run_time)),121) NextSchedule

   FROM msdb.dbo.sysjobs j

   INNER JOIN msdb.dbo.sysjobschedules s 

   ON s.job_id = j.job_id

d. Job End time from 

SubQuery1 

 to be continued..

wonder what is the difference between import math and from math

Hey you people, Hope you are having a lot of fun with SQL as ever. Well I have been trying my hands at python at last. Well wanted to since a decade now even before this bubble of AI and ML but yea end justifies the means. So I looked into some of the codes in the header with importing libraries just like #include in c. There were some confusing statement some stated with import and other stated with from so I went ahead and verifiied. It is not very unlike how we import libraries in C# or andy Other Lnaguage. Where you get the option of importing the whole library or the specific function. Like import math # will iclude all the function built into the class or the library of math form math import sqrt #will only import the sqrt funtion from the math class. I guess this will make the code lighter and stop and confusion when multiple classes imported have the same library. Thanks, Tushar

SQL Replication

-- to get what are the errors that are in the server
select  * from distribution..MSrepl_errors with(nolock) where time > '20120221' order by time desc
select  top 10 * from distribution..MSrepl_errors with(nolock)  order by time desc
---------------------------------------------------------------------------------

-- to get what was the command that was cauing the issue. From here we get the hint what is the table that is involved.
exec distribution..sp_browsereplcmds @xact_seqno_start='0x0001492D0000299B000600000000', @xact_seqno_end='0x0001492D0000299B000600000000'

{CALL [sp_MSdel_dbot_server_status] (53966)}

msdb table  which has replication issues
-------
select * from sysreplicationalerts
select distinct subscriber,article from sysreplicationalerts
select distinct publisher,publisher_db,article from sysreplicationalerts
select distinct 'select count(*) '+''''+article+''''+'from ' +publisher_db + '..'+ article + ' with (nolock)' from sysreplicationalerts



-- to run a job from command line.
EXEC msdb.dbo.sp_start_job N'FESCOVAWSA04W3-Aarow_acp-aarow_acp-11' ;
GO

--Adding transactional article in an existing publication. Here a lot of parameters are environment specific and t_aaflc_fund_fmly_lkup
--is the table that is being added to the existing publishing.
use [Aarow_acp]
exec sp_addarticle @publication = N'aarow_acp',
@article = N't_aaflc_fund_fmly_lkup',
@source_owner = N'dbo',
@source_object = N't_aaflc_fund_fmly_lkup',
@type = N'logbased',
@description = N'',
@creation_script = N'',
@pre_creation_cmd = N'drop',
@schema_option = 0x000000000803509F,
@identityrangemanagementoption = N'manual',
@destination_table = N't_aaflc_fund_fmly_lkup',
@destination_owner = N'dbo',
@status = 24,
@vertical_partition = N'false',
@ins_cmd = N'CALL [sp_MSins_dbot_aaflc_fund_fmly_lkup]',
@del_cmd = N'CALL [sp_MSdel_dbot_aaflc_fund_fmly_lkup]',
@upd_cmd = N'SCALL [sp_MSupd_dbot_aaflc_fund_fmly_lkup]'
GO


-- to compare data on row basis. using -c to compare table data on column basis.
tablediff -sourceserver PWIBOSSQLA10V3 -sourcedatabase RSOWDistribution2 -sourcetable t_server_status -destinationserver PWIBOSSQLA10V3 -destinationdatabase RSOWDistribution1 -destinationtable t_server_status -q -o


select log_reuse_wait_stats,name from sys.databases --- check if replication appears in the description

distribution.dbo.msrepl_commands

----------------------------
to find the ditributor/subscriber/publisher detail

select article,publisher_db from distribution..msarticles
select * from distribution..MSpublisher_databases
select * from distribution..MSsync_states
select * from distribution..MSsubscriber_info

IHsubscriptions
MSsubscriber_info

http://saveadba.blogspot.com/2011/08/adding-new-article-without-generating.html


Thanks,
Tushar Kanti

Identify the Database Server which is used to mount the database in Sharepoint 2010

Hi Guys,
Presently working in 2nd largest Sharepoint Environment globally. I have been working with a lot of Sharepoint Enironments with different levels of complexities. Well Today I will talk about the a couple of important tables in the Config database related to Sharepoint.
As you know the config database houses a lot of important tables. These tables have a lot of information which can be very crucial in understanding the links between a lot of internals inside sharepoint. Today we will take a peek into the Objects, Class and the SiteMap tables in the COnfig database. I definitely do miss the documentation on these tables. However there were little bread crums over the internet which I could follow and reach to this juncture.
The article is divided into two parts in the first part we will look  into the very basics of queries and in the next section we will look into the complex query merging a couple of the basic queries.

PART 1

a.SiteMap Table will have most of the information required for the Sharepoint to maintain a SiteCollection. ApplicationId,DatabaseId,Status,Version,HostHeaderIsSiteName, SubscriptionId,etc colums are there which are very useful in SiteMap table.

b.Objects Table will have the entries for most the objects that are related to config may be the Server Names,Databases,etc information.  Id,ClassId, ParentId , Name ,Status ,Version etc are colmns which are very useful in Objects table.

c.CLasses Table will have the Id,BaseClassId and FullName columns which will be useful to get basic information.

To Identify The Databases mounted in the Farm:

Query1 -> Select distinct DatabaseID from SiteMap

This Query1 will give us the databaseid's mounted for the whole farm sharing the config database.

Query2 -> Select distinct Name as DBName , DatabaseId,ParentId

from SiteMap s join Objects o on s.DatabaseID=o.ID

This Query2 will give us  the database names for the Farm that are presently mounted.

These two queries are good to start with the inquition of database information inside Sharepoint Config database.

PART2

Now lets move to the next section of this post where we will discuss other details about the database servers that are being used in Sharepoint to Mount the databases.

Query 3 -> select distinct O.Name As DatabaseName, servers.Name as ServerName

from SiteMap s

inner join Objects o on o.id= s.databaseid

inner join Objects Parent on Parent.Id= o.ParentId

inner join Objects servers on servers.Id= Parent.ParentId

This query will hunt for the parentid to which the database is belonging and then hunt for the child for the Servers and joining them we identify the Databse Servers for our Databases. Here we are self joining the Objects table to get all the information.

Thanks,

Tushar Kanti



DBA for your Data

Hi,
What is the most important resource in the planet?
 As the title might have already hinted it is "The Data". Data is the New Oil. It is the costliest resource known to humans. There are so many things which mention about the importance of Data.
But I am not here to pile on with the same information. I am here to tell you something more important.
The Guardian of your Data is Database Administrator also known as DBA. But more than often people in IT ignore this profile and grant the role to either a Windows Admin or a SQL Developer.
Imagine handing over Windows Administration to SQL Developer and SQL Developer role to Windows Administrator. Right it gives you a kick immediately. You are ready to give a knee jerk reaction. But no one understands the Database like your Friendly Neighborhood DBA.

The basic problem of IT Management hiring a DBA is giving the responsiblity to HR (who has no clue what SQL Server, MYSSQL or Oracle and let's not get started on cosmos, mongo, dynamodb). I recently read an article where a company HR posted a 12 years experience for Kubernetes. Seems like Google were 6 years too slow. The next problem is job interviews unless you have a DBA who is taking your interview (I am lucky to have some amazing interviews with very senior DBA's) you have no clue what answer the interviewer is looking for. Most of the times they will bring up very specific questions with minimal details like the  tempdb is full. Well there is no right answer and it is more of a approach the problem answer rather than a bullet point this is how it is done.

Now let's say the poor DBA still manages to siphon through the HR's list of desirable candidates and even manages to convince the non-DBA techies(no offence I love Windows Admins and SQL Developers) to impress. The company does not find much value in hiring a DBA for a single project and hence asks the person to be shared with multiple projects and the DBA loves to have more no problem there. The actual problem is no one is really ready for any change the DBA wants to do to improve security, performance or profitability. So after some time the organization finding no value in the work done asks to turn roles to a SQL Developer so they can justify the time and money spent on a DBA.

So you see the person looking after the most important resource is not even close to what he/she should have achieved. That said it is not the story of every DBA and IT of late has started to get really respectful with Data and there are even very specific and specialized profiles now related to Data like Data Scientist ,Machine Learning Engineer and so on. In the core all these profiles have a DBA in them but they specialize in specific discipline.

So there it is my view about the DBA for your Data.

Thanks,
Tushar Kanti