Friday, July 29, 2022

Convert From date and to date in Julian , JD Edwards in VBA

 

    Dim FrmDateImp

    Dim ToDateImp

    Dim tmpToDateImp

 

   

    FrmDateImp = Right(STR(PERIOD_BEG), 2) & "/01/" & Mid(str(PERIOD_BEG), 2, 4)

    tmpToDateImp = Right(STR(PERIOD_END), 2) & "/01/" & Mid(str(PERIOD_END), 2, 4)

    ToDateImp = DateSerial(YEAR(tmpToDateImp), Month(tmpToDateImp) + 1, 0)

 

   

    Dim FrmDate_DGJ As Long

    Dim ToDate_DGJ As Long

  FrmDate_DGJ = ((DatePart("YYYY", FrmDateImp) - 1900) * 1000) + DatePart("y", FrmDateImp)

ToDate_DGJ = ((DatePart("YYYY", ToDateImp) - 1900) * 1000) + DatePart("y", ToDateImp)

QryDateSummaryStr = "GLDGJ BETWEEN '" & FrmDate_DGJ & "' and '" & ToDate_DGJ & "'"


Wednesday, July 27, 2022

Change Date Format Excel Formula

 Change date format excel formula, this is good for developer too, who wants to create SQL statement from excel cell.


=TEXT(B3,"mm/dd/yyyy")


=TEXT(B3,"mm-dd-yyyy")


Tuesday, July 26, 2022

Dynamic SQL table columns for dates in Aging report, SQL Server

 

How to create Dynamic SQL table columns for dates in Aging report JD Edwards, SQL Server 


DECLARE @SQL_STR VARCHAR(8000)

DECLARE @DueDate VARCHAR(80)

DECLARE @DueDate14 VARCHAR(80)

DECLARE @DueDate28 VARCHAR(80)

DECLARE @DueDateAfter28 VARCHAR(80)

SET @DueDate =  '['+'Due as of ' +CONVERT(VARCHAR(10), GETDATE()+1, 101)+'],'

SET @DueDate14 =  '['+'Due as of ' +CONVERT(VARCHAR(10), GETDATE()+14, 101)+'],'

SET @DueDate28 =  '['+'Due as of ' +CONVERT(VARCHAR(10), GETDATE()+28, 101)+'],'

SET @DueDateAfter28 =  '['+'Due After ' +CONVERT(VARCHAR(10), GETDATE()+28, 101)+'],'


SET @SQL_STR = 

'SELECT RPAN8 AS Supplier_Number, sup.ABALPH AS Supplier_name, 

RPPST AS Pay_Stat, RPPYIN AS Pay_Inst, RPAN8 AS Payee_Number, PR.ABALPH AS Payee_Name,

RPDCT AS Doc_Type, RPDOC AS Doc_Number, RPSFX AS ITM, RPKCO AS CO, RPVINV AS Invoice_Number,

[dbo].[JDT_JTG](RPDIVJ) AS Invoice_Date,

[dbo].[JDT_JTG](RPDDJ) AS Due_Date,

CASE WHEN [dbo].[JDT_JTG](RPDDJ) BETWEEN CONVERT(VARCHAR(10), GETDATE(), 101) and  CONVERT(VARCHAR(10), GETDATE()+1, 101)

     THEN RPAG/100

ELSE 0 END '+ @DueDate + '

CASE WHEN [dbo].[JDT_JTG](RPDDJ) BETWEEN CONVERT(VARCHAR(10), GETDATE()+2, 101) and  CONVERT(VARCHAR(10), GETDATE()+14, 101)

     THEN RPAG/100

ELSE 0 END '+ @DueDate14 + '

CASE WHEN [dbo].[JDT_JTG](RPDDJ) BETWEEN CONVERT(VARCHAR(10), GETDATE()+15, 101) and  CONVERT(VARCHAR(10), GETDATE()+28, 101)

     THEN RPAG/100

ELSE 0 END '+ @DueDate28 + '

CASE WHEN [dbo].[JDT_JTG](RPDDJ) > CONVERT(VARCHAR(10), GETDATE()+28, 101) 

     THEN RPAG/100

ELSE 0 END AS '+ @DueDateAfter28 + '

CASE WHEN [dbo].[JDT_JTG](RPDDJ) >= CONVERT(VARCHAR(10), GETDATE(), 101) 

     THEN RPAG/100

ELSE 0 END AS Total_Due


FROM MFDB.PRODDTA.F0411

LEFT OUTER JOIN MFDB.PRODDTA.F0101 Sup ON Sup.ABAN8 = RPAN8

LEFT OUTER JOIN MFDB.PRODDTA.F0101 PR ON PR.ABAN8 = RPAN8

WHERE RPFY = 22 

--AND rpan8 = 642564 

AND RPPST = ''A'';


'

--PRINT (@SQL_STR)

EXEC (@SQL_STR)


Saturday, July 23, 2022

Convert Calendar Date to Julian Date Excel VBA (JD Edwards style Date)

Convert Calendar Date to Julian Date Excel VBA (JD Edwards style Date)

   Dim myDate As Date

    myDate = "07/23/2022"

    Debug.Print (((DatePart("YYYY", myDate)) - 1900) * 1000) + DatePart("y", myDate)

Wednesday, June 29, 2022

find a table column in stored procedure in SQL Server

 SELECT  distinct OBJECT_NAME (id)

FROM syscomments

WHERE text LIKE '%GLMCU%'

GO

Tuesday, June 14, 2022

DROP INDEX SQL Server

 DROP INDEX SQL Server if exists


DROP INDEX IF EXISTS IDX_tbl_XXXData_RR ON tbl_XXXData_RR;


Monday, June 13, 2022

How to load Sql queries in SQL Server table

 Sometimes we need to load SQL code in a sql table and run that code in any application. you can load long SQL code using following:-

1. make your query in one line.

2.Open an excel and add a column name in excel, paste that query in a excel cell.

3. Load this excel in SQL server using sql import wizard.

4.  Create an insert statement to load in your main table with all the fields.

INSERT INTO table_sql(sqlname,sqldesc,date,user,sqlcode)

Select sqlname,sqldesc,date,user, Column01 sqlcode from YourUpoadedTable





Wednesday, May 25, 2022

Schema LEVEL SECURITY ,SQL Server

SET NOCOUNT ON DECLARE @sql VARCHAR(MAX) DECLARE @strHTML VARCHAR(MAX) DECLARE @i INT DECLARE @rc INT DECLARE @dbname VARCHAR(400) -----------------Print header of the report-------------------- SELECT @strHTML = ' Security list for the auditor
' PRINT @strHTML -----------------Login information------------------------------------------------------------- SELECT ROW_NUMBER () OVER (ORDER BY name) AS RowNumber, name, dbname,language, CONVERT(CHAR(10),CASE denylogin WHEN 1 THEN 'X' ELSE '--' END) AS IsDenied, CONVERT(CHAR(10),CASE isntname WHEN 1 THEN 'X' ELSE '--' END) AS IsWinAuTHENtication, CONVERT(CHAR(10),CASE isntgroup WHEN 1 THEN 'X' ELSE '--' END) AS IsWinGroup, createdate,UPDATEdate, CONVERT(VARCHAR(2000), CASE sysadmin WHEN 1 THEN 'sysadmin,' ELSE '' END + CASE securityadmin WHEN 1 THEN 'securityadmin,' ELSE '' END + CASE serveradmin WHEN 1 THEN 'serveradmin,' ELSE '' END + CASE setupadmin WHEN 1 THEN 'setupadmin,' ELSE '' END + CASE processadmin WHEN 1 THEN 'processadmin,' ELSE '' END + CASE diskadmin WHEN 1 THEN 'diskadmin,' ELSE '' END + CASE dbcreator WHEN 1 THEN 'dbcreator,' ELSE '' END + CASE bulkadmin WHEN 1 THEN 'bulkadmin' ELSE '' END ) AS ServerRoles INTO #syslogins FROM master..syslogins WITH (nolock) ORDER BY name SET @rc = @@rowcount SELECT @strHTML = '
Server ' + @@servername + '

' PRINT @strHTML PRINT '
' -- Query the data only if there are rows: IF @rc = 0 BEGIN PRINT '' PRINT '' END ELSE BEGIN UPDATE #syslogins SET ServerRoles = SUBSTRING(ServerRoles,1,LEN(ServerRoles)-1) WHERE SUBSTRING(ServerRoles,LEN(ServerRoles),1) = ',' UPDATE #syslogins SET ServerRoles = '--' WHERE LTRIM(RTRIM(ServerRoles)) = '' PRINT '
Logins information
There are no logins on this server
' PRINT '' PRINT '' SET @i = 1 WHILE @i <= @rc BEGIN SELECT @strHTML = '' + '' + '' + '' + '' + '' + '' + '' + '' + '' FROM #syslogins WHERE RowNumber = @i PRINT @strHTML SET @i = @i + 1 END PRINT '
Logins information
Login Name Default DB Language Denied acess? Windows Auth? Window group? Date created Date UPDATEd Server roles
' + CONVERT(VARCHAR(50),name) + ' ' + CONVERT(VARCHAR(50),CASE ISNULL(dbname,'--') WHEN '' THEN '--' ELSE ISNULL(dbname,'--') END) + ' ' + CONVERT(VARCHAR(50),ISNULL(language,'--')) + ' ' + CONVERT(VARCHAR(10),ISNULL(IsDenied,'--')) + ' ' + CONVERT(VARCHAR(10),ISNULL(IsWinAuTHENtication,'--')) + ' ' + CONVERT(VARCHAR(10),ISNULL(IsWinGroup,'--')) + ' ' + CONVERT(VARCHAR(30),ISNULL(createdate,'--')) + ' ' + CONVERT(VARCHAR(30),ISNULL(UPDATEdate,'--')) + ' ' + CONVERT(VARCHAR(100),ISNULL(ServerRoles,'--')) + '

Back To Top ^

' PRINT'

' END DROP TABLE #syslogins ---------------Fetch data per database------------------------------------------------- CREATE TABLE #LoginMap (LoginName VARCHAR(200), UserName VARCHAR(200) NULL) CREATE TABLE #RoleUser (RoLEName VARCHAR(200), UserName VARCHAR(200) NULL) CREATE TABLE #ObjectPerms (RowNumber INT IDENTITY, UserName VARCHAR(50), PerType VARCHAR(10),PermName VARCHAR(30), SchemaName VARCHAR(50), ObjectName VARCHAR(100), ObjectType VARCHAR(20), ColName VARCHAR(50), IsGrantOption VARCHAR(10)) CREATE TABLE #DatabasePerms (RowNumber INT IDENTITY,UserName VARCHAR(50),PermType VARCHAR(20),PermName VARCHAR(50),IsGrantOption VARCHAR(5)) DECLARE dbs CURSOR FOR SELECT name FROM master..sysdatabases ORDER BY name OPEN dbs FETCH NEXT FROM dbs INTO @dbname WHILE @@FETCH_STATUS = 0 BEGIN TRUNCATE TABLE #LoginMap TRUNCATE TABLE #RoleUser TRUNCATE TABLE #ObjectPerms TRUNCATE TABLE #DatabasePerms SELECT @strHTML = '
Database ' + @dbname + '

' PRINT @strHTML -----------------Mapping of logins to users------------------ EXEC(' INSERT INTO #LoginMap SELECT login.loginname,users.name FROM ['+ @dbname+'].dbo.sysusers users INNER JOIN [master].[dbo].[syslogins] login ON users.[sid] = login.[sid] WHERE users.uid < 16382 and users.name not in (''public'',''dbo'',''guest'') ') SET @strHTML = '' PRINT '
' --Query the data only if there are rows IF NOT EXISTS (SELECT 1 FROM #LoginMap) BEGIN PRINT '' PRINT '' END ELSE BEGIN PRINT '' PRINT '' SELECT @strHTML = @strHTML + '' + CHAR(10) FROM #LoginMap ORDER BY LoginName PRINT @strHTML END PRINT '
Mapping of logins to users
There are no mappings in this database
Mapping of logins to users
Login Name User Name
' + CONVERT(VARCHAR(50),LoginName) + ' ' + CONVERT(VARCHAR(50),ISNULL(UserName,'')) + '

Back To Top ^

' ----------------SQL roles per user------------------ EXEC ('INSERT INTO #RoleUser SELECT b.name AS Role_name, a.name AS User_name ' + 'FROM ['+ @dbname+']..sysusers a ' + 'INNER JOIN ['+ @dbname+ ']..sysmembers c on a.uid = c.memberuid ' + 'INNER JOIN ['+ @dbname+ ']..sysusers b ON c.groupuid = b.uid ' + 'WHERE a.name <> ''dbo''' ) SET @strHTML = '' PRINT '
' -- Query the data only if there are rows: IF NOT EXISTS(SELECT 1 FROM #RoleUser) BEGIN PRINT '' PRINT '' END ELSE BEGIN PRINT '' PRINT '' SELECT @strHTML = @strHTML + '' + CHAR(10) FROM #RoleUser ORDER BY RoLEName PRINT @strHTML END PRINT '
Roles per user
There are no users mapped to roles in this database
Roles per user
Role Name User Name
' + CONVERT(VARCHAR(50),RoLEName) + ' ' + CONVERT(VARCHAR(50),ISNULL(UserName,'')) + '

Back To Top ^

' ----------------Database level Permissions------------------------- EXEC ('INSERT INTO #DatabasePerms (UserName,PermType,PermName,IsGrantOption) SELECT usr.name, CASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''GRANT'' END, perm.permission_name, CASE WHEN perm.state != ''W'' THEN ''--'' ELSE ''X'' END AS IsGrantOption FROM ['+@dbname+'].sys.database_permissions AS perm INNER JOIN ['+@dbname+'].sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id WHERE perm.major_id = 0 ORDER BY usr.name, perm.permission_name ASC, perm.state_desc ASC' ) SET @rc = @@rowcount PRINT '
' -- Query the data only if there are rows: IF NOT EXISTS(SELECT 1 FROM #DatabasePerms) BEGIN PRINT '' PRINT '' END ELSE BEGIN PRINT '' PRINT '' SET @i = 1 WHILE @i <= @rc BEGIN SELECT @strHTML = '' + '' + '' + ''+ '' FROM #DatabasePerms WHERE Rownumber = @i PRINT @strHTML SET @i = @i + 1 END END PRINT '
Database level permissions
There are no specific permissions on the database level
Database level permissions
User Name Permission type Permission Name Grant option?
' + CONVERT(VARCHAR(50),UserName) + ' ' + CONVERT(VARCHAR(50),ISNULL(PermType,'--')) + ' ' + CONVERT(VARCHAR(50),ISNULL(PermName,'--')) + ' ' + CONVERT(VARCHAR(5),ISNULL(IsGrantOption,'--')) + '

Back To Top ^

' ----------------Database object Permissions------------------------- EXEC ('INSERT INTO #ObjectPerms (UserName,PerType,PermName,SchemaName,ObjectName,ObjectType,ColName,IsGrantOption) SELECT usr.name AS UserName, CASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''GRANT'' END AS PerType, perm.permission_name,USER_NAME(obj.schema_id) AS SchemaName, obj.name AS ObjectName, CASE obj.Type WHEN ''U'' THEN ''Table'' WHEN ''V'' THEN ''View'' WHEN ''P'' THEN ''Stored Proc'' WHEN ''FN'' THEN ''Function'' ELSE obj.Type END AS ObjectType, CASE WHEN cl.column_id IS NULL THEN ''--'' ELSE cl.name END AS ColName, CASE WHEN perm.state = ''W'' THEN ''X'' ELSE ''--'' END AS IsGrantOption FROM ['+@dbname+'].sys.database_permissions AS perm INNER JOIN ['+@dbname+'].sys.objects AS obj ON perm.major_id = obj.[object_id] INNER JOIN ['+@dbname+'].sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id LEFT JOIN ['+@dbname+'].sys.columns AS cl ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id WHERE obj.Type <> ''S'' ORDER BY usr.name, perm.state_desc ASC, perm.permission_name ASC' ) SET @rc = @@rowcount PRINT '
' -- Query the data only if there are rows: IF NOT EXISTS(SELECT 1 FROM #ObjectPerms) BEGIN PRINT '' PRINT '' END ELSE BEGIN PRINT '' PRINT '' SET @i = 1 WHILE @i <= @rc BEGIN SELECT @strHTML = '' + '' + '' + '' + '' + '' + '' + '' FROM #ObjectPerms WHERE Rownumber = @i PRINT @strHTML SET @i = @i + 1 END END PRINT '
Object permissions
There are no specific permissions to objects in this database
Object permissions
User Name Permission type Permission Name Schema Name Object Name Object type type Column Name Grant option?
' + CONVERT(VARCHAR(50),UserName) + ' ' + CONVERT(VARCHAR(50),ISNULL(PerType,'--')) + ' ' + CONVERT(VARCHAR(50),ISNULL(PermName,'--')) + ' ' + CONVERT(VARCHAR(50),ISNULL(SchemaName,'--')) + ' ' + CONVERT(VARCHAR(50),ISNULL(ObjectName,'--')) + ' ' + CONVERT(VARCHAR(30),ISNULL(ObjectType,'--')) + ' ' + CONVERT(VARCHAR(50),ISNULL(ColName,'--')) + ' ' + CONVERT(VARCHAR(5),ISNULL(IsGrantOption,'--')) + '

Back To Top ^

' FETCH NEXT FROM dbs INTO @dbname END ---------------Close cursor and drop all temporary objects------------- CLOSE dbs DEALLOCATE dbs DROP TABLE #LoginMap DROP TABLE #RoleUser DROP TABLE #ObjectPerms DROP TABLE #DatabasePerms PRINT '' GO https://www.mssqltips.com/sqlservertip/1818/script-to-auto-generate-a-security-report-for-your-sql-server-instance/

Sunday, May 15, 2022

Crystal Report Check box

Crystal Report does not have built in support to display a check box for Boolean operators or to display a radio button, but it can be achieved as explained below: The simplest way to accomplish this task is to 1) Edit the display string of the value in Format Editor as shown below. https://blogs.sap.com/2012/05/18/how-to-display-a-check-box-and-a-radio-button-in-crystal-report-2011/

Thursday, April 7, 2022

SQL SERVER – Find Last Date Time Updated for a Table

 


SELECT OBJECT_NAME(OBJECT_ID) AS TableName,

 last_dateTime_update,*

FROM sys.dm_db_index_usage_stats

WHERE database_id = DB_ID( 'PRODDTA')

AND OBJECT_ID=OBJECT_ID('TBLWDCData')

Wednesday, March 30, 2022

Passing Parameter in a stored Proc VB.Net

 


you have to add this

    sqlComm.Parameters.AddWithValue("FirstName", txtName.Text)



Private Sub InsertNewRecord()

sqlCon = New SqlConnection(strConn)

Using (sqlCon)

            Dim sqlComm As New SqlCommand()

            sqlComm.Connection = sqlCon

sqlComm.CommandText = "InsertDataIntoTable"

            sqlComm.CommandType = CommandType.StoredProcedure

sqlComm.Parameters.AddWithValue("FirstName", txtName.Text)

            sqlComm.Parameters.AddWithValue("Surname", txtSurname.Text)

            sqlComm.Parameters.AddWithValue("Age", Integer.Parse(txtAge.Text))

            sqlCon.Open()

            sqlComm.ExecuteNonQuery()

        End Using

End Sub


Source: https://www.codeguru.com/visual-basic/using-sql-stored-procedures-with-vb-net


Monday, March 14, 2022

Get Full SQL permission List

 ;WITH 

[explicit] AS (

   SELECT [p].[principal_id], [p].[name], [p].[type_desc], [p].[create_date], [p].[is_disabled],

         [dbp].[permission_name] COLLATE SQL_Latin1_General_CP1_CI_AS [permission],

         CAST('' AS SYSNAME) [grant_through]

   FROM [sys].[server_permissions] [dbp]

   INNER JOIN [sys].[server_principals] [p] ON [dbp].[grantee_principal_id] = [p].[principal_id]

   --WHERE ([dbp].[type] IN ('CL','TO','IM','ADBO') OR [dbp].[type] LIKE 'AL%')

   --  AND [dbp].[state] IN ('G','W')

   UNION ALL

   SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [dp].[is_disabled], [p].[permission], [p].[name] [grant_through]

   FROM [sys].[server_principals] [dp]

   INNER JOIN [sys].[server_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]

   INNER JOIN [explicit] [p] ON [p].[principal_id] = [rm].[role_principal_id]

   ),

[fixed] AS (

   SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [dp].[is_disabled], [p].[name] [permission], CAST('' AS SYSNAME) [grant_through]

   FROM [sys].[server_principals] [dp]

   INNER JOIN [sys].[server_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]

   INNER JOIN [sys].[server_principals] [p] ON [p].[principal_id] = [rm].[role_principal_id]

   --WHERE [p].[name] IN ('sysadmin','securityadmin','bulkadmin')

   UNION ALL

   SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [dp].[is_disabled], [p].[permission], [p].[name] [grant_through]

   FROM [sys].[server_principals] [dp]

   INNER JOIN [sys].[server_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]

   INNER JOIN [fixed] [p] ON [p].[principal_id] = [rm].[role_principal_id]

   )

SELECT DISTINCT [name], [type_desc], [create_date], [is_disabled], [permission], [grant_through]

FROM [explicit]

--WHERE [type_desc] NOT IN ('SERVER_ROLE')

--  AND [name] NOT IN ('sa','SQLDBO','SQLNETIQ')

--  AND [name] NOT LIKE '##%'

--  AND [name] NOT LIKE 'NT SERVICE%'

--  AND [name] NOT LIKE 'NT AUTHORITY%'

--  AND [name] NOT LIKE 'BUILTIN%'

UNION ALL

SELECT DISTINCT [name], [type_desc], [create_date], [is_disabled], [permission], [grant_through]

FROM [fixed]

--WHERE [type_desc] NOT IN ('SERVER_ROLE')

--  AND [name] NOT IN ('sa','SQLDBO','SQLNETIQ')

--  AND [name] NOT LIKE '##%'

--  AND [name] NOT LIKE 'NT SERVICE%'

--  AND [name] NOT LIKE 'NT AUTHORITY%'

--  AND [name] NOT LIKE 'BUILTIN%'

ORDER BY 1

OPTION(MAXRECURSION 10)


CREATE TABLE #Info([database] SYSNAME, [username] SYSNAME, [type_desc] NVARCHAR(60), [create_date] DATETIME, [permission] SYSNAME, [grant_through] SYSNAME)

DECLARE @cmd VARCHAR(MAX)

SET @cmd = ''

SELECT @cmd = @cmd + 'INSERT #Info EXEC(''

USE ['+[name]+']

;WITH 

[explicit] AS (

   SELECT [p].[principal_id], [p].[name], [p].[type_desc], [p].[create_date],

         [dbp].[permission_name] COLLATE SQL_Latin1_General_CP1_CI_AS [permission],

         CAST('''''''' AS SYSNAME) [grant_through]

   FROM [sys].[database_permissions] [dbp]

   INNER JOIN [sys].[database_principals] [p] ON [dbp].[grantee_principal_id] = [p].[principal_id]

   WHERE ([dbp].[type] IN (''''IN'''',''''UP'''',''''DL'''',''''CL'''',''''DABO'''',''''IM'''',''''SL'''',''''TO'''') OR [dbp].[type] LIKE ''''AL%'''' OR [dbp].[type] LIKE ''''CR%'''')

     AND [dbp].[state] IN (''''G'''',''''W'''')

   UNION ALL

   SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [p].[permission], [p].[name] [grant_through]

   FROM [sys].[database_principals] [dp]

   INNER JOIN [sys].[database_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]

   INNER JOIN [explicit] [p] ON [p].[principal_id] = [rm].[role_principal_id]

   ),

[fixed] AS (

   SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [p].[name] [permission], CAST('''''''' AS SYSNAME) [grant_through]

   FROM [sys].[database_principals] [dp]

   INNER JOIN [sys].[database_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]

   INNER JOIN [sys].[database_principals] [p] ON [p].[principal_id] = [rm].[role_principal_id]

   --WHERE [p].[name] IN (''''db_owner'''',''''db_datareader'''',''''db_datawriter'''',''''db_ddladmin'''',''''db_securityadmin'''',''''db_accessadmin'''')

   UNION ALL

   SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [p].[permission], [p].[name] [grant_through]

   FROM [sys].[database_principals] [dp]

   INNER JOIN [sys].[database_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]

   INNER JOIN [fixed] [p] ON [p].[principal_id] = [rm].[role_principal_id]

   )

SELECT DB_NAME(), [name], [type_desc], [create_date], [permission], [grant_through]

FROM [explicit]

--WHERE [type_desc] NOT IN (''''DATABASE_ROLE'''')

UNION ALL

SELECT DB_NAME(), [name], [type_desc], [create_date], [permission], [grant_through]

FROM [fixed]

--WHERE [type_desc] NOT IN (''''DATABASE_ROLE'''')

OPTION(MAXRECURSION 10)

'');'

FROM [sys].[databases]

--WHERE [state_desc] = 'ONLINE'

EXEC (@cmd)

SELECT DISTINCT *

FROM #Info

--WHERE [username] NOT IN ('dbo','guest','SQLDBO')

--  AND [username] NOT LIKE '##%'

--  AND [database] NOT IN ('master','model','msdb','tempdb')

ORDER BY 1, 2

DROP TABLE #Info


source 

https://www.mssqltips.com/sqlservertip/6145/sql-server-permissions-list-for-read-and-write-access-for-all-databases/



Monday, February 21, 2022

How to enter PTO(Time-Off) in Kronos for your Employee

 How to enter PTO in Kronos for your Employee 

Go To Manage Schedule-> Click on Full Schedule->Select employee->right click on day->Enter time-off

-> Click on Request Time of ->Click on Apply-> after that Enter Time off-> Review and submit.


Friday, February 18, 2022

To find if a particulate table column changed in SQL Server

 


SELECT
OBJECT_NAME(a.[object_id]) as [table_Name]
,a.[name] as [column_Name]
,b.modify_date
, b.create_date
FROM [sys].[columns] a
JOIN [sys].[objects] b
ON a.[object_id] = b.[object_id]
where OBJECT_NAME(a.[object_id])='F0911'
ORDER BY b.modify_date DESC

Monday, February 14, 2022

2 Digit Year in SQL Server


FORMAT(DATEADD(year, 1, getdate()), 'yy') 

-- In Select statement
Select FORMAT(DATEADD(year, 0, getdate()), 'yy')  from PRODDTA.F0010 where ccco='00000'

also you can add one year 
FORMAT(DATEADD(year, 1, getdate()), 'yy')